Remote Connection to TiDB

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB远程连接

| username: lyf云飞

How do I connect to TiDB remotely? I want to use MySQL on another computer to connect to TiDB, but the connection is being refused. Do I need to enable some permissions?

| username: TiDBer_CQ | Original post link

TiDB is actually similar to MySQL in enabling remote access. You can perform the following operations in the command line mode on the TiDB Server machine. If the network is accessible, you can connect to TiDB remotely.

use mysql;
alter user 'root'@'%' identified by 'password';
flush privileges;
| username: 啦啦啦啦啦 | Original post link

Check if port 4000 is open, whether the account has remote connection permissions, and please provide the specific error message.

| username: 裤衩儿飞上天 | Original post link

How did you connect?

| username: tidb菜鸟一只 | Original post link

First, try using telnet to check if port 4000 is accessible on the computer where TiDB is installed from another computer.

| username: lyf云飞 | Original post link

When entering alter user 'root'@'%' identified by 'password';, it reports ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 23 near "%' identified by 'password'". How can I solve this error?

| username: Kongdom | Original post link

Note the Chinese punctuation marks, which need to be changed to English ones.
Additionally, this command is a script to modify the root password of the database user. The “password” at the end should be replaced with the login password you want to set.

If you want to change the password, you can also try this script:
update mysql.user set authentication_string=password(‘your_password’) where user=‘root’;
flush privileges;

| username: 会飞的土拨鼠 | Original post link

You need to create a database user with % permissions (remote access permissions).

| username: 会飞的土拨鼠 | Original post link

Create an account with administrator privileges

create user ‘ncayusys’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘QhmHJn2gZIE7’;
– Specify database
grant select, update, insert on ncayudb.* to ‘ncayusys’@‘%’;
– All databases
grant select, update, insert on . to ‘ncayusys’@‘%’;
flush privileges;

Connect to TiDB using MySQL

mysql -h 192.168.227.134 -P 4000 -u ncayusys -p

Backup database ncayudb

/data1/tidbbackup/mysql57/bin/mysqldump -h 192.168.227.134 -P 4000 -u ncayusys -p ncayudb | gzip > /data/tidbbackup/ncayudb.sql.gz

| username: 会飞的土拨鼠 | Original post link

View user permissions

select user,host from mysql.user;

Change the “localhost” permission of the root account to “%” permission

update user set host = “%” where user = “root” and host = “localhost”;

Change the “%” permission of the root account to “localhost” permission; the root account can only be used locally

mysql> update user set host = “localhost” where user = “root” and host = “%”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Change password:

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘Ncayu@bNuPc3’;

Refresh

flush privileges;

Test if the account password can be logged in normally after modification

mysql -u root -p Ncayu@bNuPc3

Hello, take a look at what you need to modify, then execute the commands.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.