FAQ rollup on Linux accessing MySQL database via MySQL command

FAQ rollup on Linux accessing MySQL database via MySQL command

1) Create login account

#创建用户并授权

#允许本地访问

Create user ' test ' @ ' localhost ' identified by ' 123456 ';

#允许外网访问

Create user ' test ' @ '% ' identified by ' 123456 ';

#grant permissions on the database. * to ' username ' @ ' login host ' identified by ' password ';

#原始密码为: 123456 The password after encryption is: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Grant all privileges on * * to ' root ' @ '% ' identified by password ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' with Grant op tion;

#用户通过本地IP访问数据库

Grant all privileges on * * to ' test ' @ ' localhost ' identified by ' 123456 ';

#用户通过外网IP访问数据库

Grant all privileges on * * to ' test ' @ ' percent ' identified by ' 123456 ';

#刷新权限

Flush privileges;

#创建数据库

Create database name;

#删除数据库

drop database name;

#删除表

drop table name;

#删除用户及权限

Drop user ' username ' @ ' localhost ';

Drop user ' username ' @ '% ';

#刷新权限

Flush privileges;

2) commands for normal login

mysql-utest-p123456-p3306-h192.168.48.129

3) MySQL command not found

[Email protected] src]$ mysqls-utest-p123456-p3306-h192.168.48.129

Bash:mysqls:command not found

4) parameter U capital Error 1064 (42000) ... use near ' st ' on line 1

[Email protected] src]$ mysql-utest-p123456-p3306-h192.168.48.129

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 1064 (42000) at line 1:you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' st ' in line 1

5) Username Error 1045 (28000) ... Access denied

[Email protected] src]$ mysql-utest1-p123456-p3306-h192.168.48.129

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 1045 (28000): Access denied for user ' test1 ' @ ' 192.168.48.129 ' (using Password:yes)

6) p is uppercase or there are spaces or password errors between the passwords error 1045 (28000) ... Access denied

[Email protected] src]$ mysql-utest-p 123456-p3306-h192.168.48.129

Enter Password:

ERROR 1045 (28000): Access denied for user ' test ' @ ' 192.168.48.129 ' (using Password:no)

[Email protected] src]$ mysql-utest-p123456-p3306-h192.168.48.129

ERROR 1045 (28000): Access denied for user ' test ' @ ' 192.168.48.129 ' (using Password:no)

[Email protected] src]$ mysql-utest-p1234567-p3306-h192.168.48.129

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 1045 (28000): Access denied for user ' test ' @ ' 192.168.48.129 ' (using Password:yes)

7) port number not ERROR 2003 (HY000): Can ' t connect to MySQL ... (111)

[Email protected] src]$ mysql-utest-p123456-p3307-h192.168.48.129

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.48.129 ' (111)

8) The address of the connection database is not ERROR 2003 (HY000): Can ' t connect to MySQL ... (113)

[Email protected] src]$ mysql-u test-p123456-p 3306-h 192.168.48.121

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.48.121 ' (113)

9) address contains port number information ERROR 2005 (HY000): Unknown MySQL ... (0)

[Email protected] src]$ mysql-u test-p123456-p 3306-h 192.168.48.129:3306

MySQL: [Warning] Using a password on the command line interface can is insecure.

ERROR 2005 (HY000): Unknown MySQL Server Host ' 192.168.48.129:3306 ' (0)

Summary of common issues when accessing MySQL database on Linux with mysql command