1. 创建用户
create user 用户名@'127.0.0.1' identified by '密码';


user 后写你要设置的用户名
@ 后可以指定IP地址,这里写127.0.0.1的意思是只有本机可以连接
by 后单引号内写密码

2. 授予增删改查权限
grant select,delete,insert,update on 数据库.* to 用户名@'127.0.0.1';


on 数据库.* 的意思是指定数据库下所有的表
to 用户名的意思是 指定那个用户访问
用户名@后的IP地址指: 这个用户从这个指定的 IP登录，拥有前面指定的库的权限

好久不用mysql了，今天拾起来，新建用户，用Navicat连接之后，发现没有新建数据库的权限。找了好久才找到方法，那就是新建用户之后授权的的时候授予用户在所有数据库上的所有权限，（当然有可能有别的方法，希望知道的前辈们指导我）语句如下：

grant all privileges on *.* to demo@localhost identified by '123456';

下面附送亲测有效的新建用户，新建数据库，用户授权，删除用户，修改密码的打包套餐：

1.新建用户

　　1.1 登录MYSQL：

　　C:\mysql\mysql-5.6.41-winx64\bin>mysql -u root -p

    Enter password: **********

　　1.2 创建用户：

　　mysql> insert into mysql.user(Host,User,Password) values("localhost","demo",password("1234"));

　　这样就创建了一个名为：demo 密码为：1234 的用户。

　　注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

　　1.3 然后登录一下：

　　mysql>exit;

　　C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

    Enter password: **********

2.为用户授权

　　授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

　　2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录：

　　C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

    Enter password: **********

　　2.2 首先为用户创建一个数据库(testDB)：

　　mysql>create database demoDB;

　　2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

　　 mysql>grant all privileges on demoDB.* to demo@localhost identified by '1234';

 　　mysql>flush privileges;//刷新系统权限表

　　格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

　　2.4 如果想指定部分权限给一用户，可以这样来写:

　　mysql>grant select,update on demoDB.* to demo@localhost identified by '1234';

　　mysql>flush privileges; //刷新系统权限表

　　2.5 授权test用户拥有所有数据库的某些权限： 　 

　　mysql>grant select,delete,update,create,drop on *.* to demo@"%" identified by "1234";

     //test用户对所有数据库都有select,delete,update,create,drop 权限。

　 //@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）

　//对localhost授权：加上一句grant all privileges on demoDB.* to demo@localhost identified by '1234';即可。 

3. 删除用户

 　    C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

    Enter password: **********

 　　mysql>Delete FROM user Where User='demo' and Host='localhost';

 　　mysql>flush privileges;

 　　mysql>drop database demoDB; //删除用户的数据库

删除账户及权限：>drop user 用户名@'%';

　　　　　　　　>drop user 用户名@ localhost; 

4. 修改指定用户密码

  　　C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

      Enter password: **********

  　　mysql>update mysql.user set password=password('新密码') where User="demo" and Host="localhost";

  　　mysql>flush privileges;

 

5. 列出所有数据库

　　mysql>show database; 

6. 切换数据库

　　mysql>use '数据库名';

7. 列出所有表

　　mysql>show tables;

 

8. 显示数据表结构

　　mysql>describe 表名;

9. 删除数据库和数据表

　　mysql>drop database 数据库名;

　　mysql>drop table 数据表名;

 

Host is not allowed to connect to this MySql Server
当“初次”在CentOS系统上(安装在虚拟机中)安装并且简单配置完MySql服务器之后，想要在本地访问虚拟机上的MySql数据库，当在本地输入mysql登陆命令后，出现如下图所示错误。
简而言之，即Host(192.168.10.1)不允许连接MySql数据库。
【障碍再现】
Host is not allowed to connect to this MySql Server
当“初次”在CentOS系统上(安装在虚拟机中)安装并且简单配置完MySql服务器之后，想要在本地访问虚拟机上的MySql数据库，当在本地输入mysql登陆命令后，出现如下图所示错误。
简而言之，即Host(192.168.10.1)不允许连接MySql数据库。
【障碍原因】
Host端的权限不足。
在MySql数据库服务器端的mysql数据库中，有一个user表，该表中就记录了可以登录MySql服务器的帐号相关信息，如下图所示。
此时，user表中的host字段仅仅有“localhost”这一条记录，则说明了MySql服务器仅仅允许从“localhost(此处的localhost指的是虚拟机)”登录到数据库。
因此，若想允许其他非localhost的用户访问，就要修改host字段的数值。
【解决方案01：改表法】将user表中host字段中的数据修改为“%”
 注意，修改完之后，要记得“刷新权限”，否则，Host端依然无法连接到MySql上。
【解决方案02：授权法】
以下几个示例可以作为有价值的参考：(自己经过测试，可以起效)
(1) 如果想要让数据库用户myDbUser01，使用密码myPassword，从"任何主机"连接到mysql服务器的话，可以使用如下命令：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myDbUser01'@'%' IDENTIFIED BY 'myPassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
(2)如果想要让数据库用户myDbUser01，使用密码myPassword，从"ip为192.168.10.1的主机"连接到mysql服务器，可以使用如下命令：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myDbUser01'@'192.168.10.1' IDENTIFIED BY 'myPassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
(3) 如果想要让数据库用户myDbUser01，使用密码myPassword，从ip为192.168.10.1的主机连接到mysql服务器的"dk数据库"，可以使用如下命令：
mysql> GRANT ALL PRIVILEGES ON dk.*TO 'myDbUser01'@'192.168.10.1' IDENTIFIED BY 'myPassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
【拓展：撤销用户权限】
命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明: privilege, databasename, tablename - 同授权部分.
例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
注意:
REVOKE语句只能取消用户的权限，而不可以删除用户。即使取消了所有的权限，用户仍然可以连接到服务器。
要想彻底的删除用户，必须使用DELETE语句将该用户的记录从MySQL数据库中的user表中删除。该语句的语法格式如下：
Delete from user where user = "user_name" and host = "host_name" ;
使用DELETE删除用户sss，代码如下：
mysql> use mysql
Database changed
mysql> delete from user where user='sss' and host='localhost' ;
mysql>flush privileges ;
Query OK, 1 row affected (0.02 sec)
其中，delete用于删除用户，flush告诉服务器重新加载授权表。

一.添加数据库管理工具phpmyadmin
[root@foundation13 ~]# scp phpMyAdmin-5.0.2-all-languages.zip server1:/usr/local/lnmp/nginx/html/phpadmin
root@server1's password: 
phpMyAdmin-5.0.2-all-languages.zip                                                                                  100%   14MB 195.1MB/s   00:00   
[root@server1 html]# ls
50x.html  bbs  download  example.php  index.html  index.php  memcache.php  phpadmin  redhat.jpg
[root@server1 html]# unzip phpadmin
[root@server1 html]# ls
50x.html  bbs  download  example.php  index.html  index.php  memcache.php  phpadmin  phpMyAdmin-5.0.2-all-languages  redhat.jpg
[root@server1 html]# rm -fr phpadmin
[root@server1 html]# mv phpMyAdmin-5.0.2-all-languages phpadmin
[root@server1 html]# ls
50x.html  bbs  download  example.php  index.html  index.php  memcache.php  phpadmin  redhat.jpg
[root@server1 html]# mv phpadmin phpmyadmin
[root@server1 html]# cd ..
[root@server1 nginx]# cd conf
[root@server1 conf]# vim nginx.conf
[root@server1 conf]# nginx -t 
nginx: the configuration file /usr/local/lnmp/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/lnmp/nginx/conf/nginx.conf test is successful
[root@server1 conf]# nginx -s reload
[root@server1 conf]# cd ..
[root@server1 nginx]# cd html
[root@server1 html]# ls
50x.html  bbs  download  example.php  index.html  index.php  memcache.php  phpmyadmin  redhat.jpg
[root@server1 html]# cd /usr/local/lnmp/php
[root@server1 php]# ls
bin  etc  include  lib  php  sbin  var
[root@server1 php]# cd lib
[root@server1 lib]# vim php.ini
[root@server1 lib]# systemctl reload php-fpm

php.ini






二.远程连接
设置远程主机可以连接
[root@server1 phpmyadmin]# pwd
/usr/local/lnmp/nginx/html/phpmyadmin
[root@server1 phpmyadmin]# vim config.inc.php
[root@server1 phpmyadmin]# nginx -s reload



创建root用户，授予对172.25.13网段数据库的全部权限
[root@server3 local]# mysql -pWestos_007
mysql> create user root@'172.25.13.%' identified by 'Westos_007';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to root@'172.25.13.%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@server3 local]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 



三.建立用户，授予指定数据库权限
[root@server3 local]# mysql -pWestos_007
mysql> create user lj@'172.25.13.%' identified by 'Westos_007';
Query OK, 0 rows affected (0.02 sec)

mysql> create database westos;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on westos.* to lj@'172.25.13.%';       //只能对westos数据库进行操作
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@server3 local]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 




在指定数据库westos中新建表



表中插入数据


三.主从同步
1.server中创建用户repl，授予复制文件权限，
[[root@server1 lib]# vim /etc/my.cnf
[root@server1 lib]# cd /usr/local/lnmp/nginx/html/phpmyadmin
[root@server1 phpmyadmin]# mysql -pWestos_007
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'repl'@'172.25.13.%' identified by 'Westos_007';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'172.25.13.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

2.设置server3的id为2（从机），重启数据库
/etc/my.cnf
[root@server1 lib]# vim /etc/my.cnf
[root@server1 phpmyadmin]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


3.server3中设置复制文件的原主机，用户，复制的日志文件，密码之后刷新数据库
[root@server3 local]# vim /etc/my.cnf
[root@server3 local]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@server3 local]# mysql -h 172.25.13.1 -urepl -pWestos_007
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

[root@server3 local]# mysql -pWestos_007
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.13.1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Westos_007',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=156,
    -> GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.13.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: server3-relay-bin.000003
                Relay_Log_Pos: 509
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 


4.编辑/etc/my.cnf,设置server1为主机，重启数据库

5.主机中新建数据库，从机可以访问到，日志文件更改
server1:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      341 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit


server3:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.13.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 341
               Relay_Log_File: server3-relay-bin.000003
                Relay_Log_Pos: 509
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| westos             |
+--------------------+
6 rows in set (0.01 sec)

mysql> exit

6.主机进行插入操作，从机可以访问到
test中插入数据





server1
[root@server1 phpmyadmin]# mysql -pWestos_007
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 83
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      778 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| client         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from client;
+--------+----------+--------+
| name   | password | city   |
+--------+----------+--------+
| 小海   |  1234566 | 成都   |
| 陈兵   |   333333 | 海南   |
+--------+----------+--------+
2 rows in set (0.00 sec)


server2
[root@server3 local]# mysql -pWestos_007
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 149
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| client         |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql> select client;
ERROR 1054 (42S22): Unknown column 'client' in 'field list'
mysql> select * from client;
+--------+----------+--------+
| name   | password | city   |
+--------+----------+--------+
| 小海   |  1234566 | 成都   |
| 陈兵   |   333333 | 海南   |
+--------+----------+--------+
2 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.13.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 778
               Relay_Log_File: server3-relay-bin.000005
                Relay_Log_Pos: 993
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: