Linux系统:CentOS Linux release 7.4.1708 (Core)
Docker版本: 17.03.0-ce, build 3a232c8
mysql :8
一、准备
1、创建主从服务器配置文件以及文件
在opt下创建mysql文件夹,以及其相关的目录结构。
1.1 主服务器目录结构以及配置文件
[root@VM_0_6_centos mysql]# pwd
/opt/mysql
[root@VM_0_6_centos mysql]# ls
master slave
[root@VM_0_6_centos mysql]# cd master/
[root@VM_0_6_centos master]# ls
cnf data
[root@VM_0_6_centos master]# cd cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../data/
[root@VM_0_6_centos data]# ls
mysql
主服务器配置文件my.cnf
[mysqld]
basedir = /opt/mysql/master/
datadir = /opt/mysql/master/data/mysql
log-bin = mysql-bin
server-id = 1
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
注意:这两项必选有
#[必须]启用二进制日志,打开Binlog日志记录,并指定文件名
log-bin = mysql-bin
#/[必须]服务器唯一ID,需要给集群中的每个服务分配一个单独的ID,默认是1,从库设置为2
server-id = 1
log_bin-index:Binlog日志文件
其他基本配置说明,如果需要可以直接在配置文件中添加。
[mysqld]
server-id=1
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
# 设置mysql的安装目录
basedir = /opt/mysql/master/
# 设置mysql数据库的数据的存放目录
datadir = /opt/mysql/master/data/mysql
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
1.2 主服务器目录结构以及配置文件
[root@VM_0_6_centos mysql]# pwd
/opt/mysql
[root@VM_0_6_centos mysql]# ls
master slave
[root@VM_0_6_centos mysql]# cd slave/
[root@VM_0_6_centos slave]# ls
cnf data
[root@VM_0_6_centos slave]# cd cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../data/
[root@VM_0_6_centos data]# ls
mysql
从服务器配置文件my.cnf
[mysqld]
basedir = /opt/mysql/slave/
datadir = /opt/mysql/slave/data/mysql
log-bin = mysql-bin
server-id = 2
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
注意:这两项必选有
#[必须]启用二进制日志,打开从服务的bin-log日志记录
log-bin = mysql-bin
#/[必须]服务器唯一ID,默认是1,从库设置为2
server-id = 2
#打开从服务的relay-log日志。
relay-log:
其他基本配置说明,如果需要可以直接在配置文件中添加。
[mysqld]
#主库和从库需要不一致
server-id=2
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir = /opt/mysql/slave/
# 设置mysql数据库的数据的存放目录
datadir = /opt/mysql/slave/data/mysql
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
二、主从服务器配置
1、启动主从服务器,并运行测试。
1.1 主服务器运行以及客户端连接测试
[root@VM_0_6_centos mysql]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 1e4405fe1ea9 4 weeks ago 437MB
mysql latest d435eee2caa5 4 weeks ago 456MB
[root@VM_0_6_centos mysql]# docker run -itd -p 3307:3306 --name master -v /opt/mysql/master/cnf:/etc/mysql/conf.d -v /opt/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=master123 d435eee2caa5
404047f180831b29cbf2f6360c835b4ec73b0cb1e729a37342ad1e0d0cc9b1f6
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
404047f18083 d435eee2caa5 "docker-entrypoint.s…" 3 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp master
[root@VM_0_6_centos mysql]# docker exec -it 404047f18083 /bin/bash
root@404047f18083:/# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@404047f18083:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
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> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | caching_sha2_password | $A$005$Npo^P0T 6KKkrgPa8dCXbzn1plB.EhBPSX3RwD/OqatHQuXsUWVhMhttEaB |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | caching_sha2_password | $A$005${m \ "q!
FG:d!
b0ISxbKLKe6bk2zHnWl4wNWkYt/3RmzFkwn/8KffCVI5 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'master123';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | mysql_native_password | *E20FA3FC93A99D845ADE17D2B27F1C360ECD3B48 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | caching_sha2_password | $A$005${m \ "q!
FG:d!
b0ISxbKLKe6bk2zHnWl4wNWkYt/3RmzFkwn/8KffCVI5 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> exit;
Bye
root@404047f18083:/# exit
exit
[root@VM_0_6_centos mysql]#
客户端连接测试:
1.2 从服务器运行以及客户端连接测试
[root@VM_0_6_centos mysql]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 1e4405fe1ea9 4 weeks ago 437MB
mysql latest d435eee2caa5 4 weeks ago 456MB
[root@VM_0_6_centos mysql]# docker run -itd -p 3308:3306 --name slave -v /opt/mysql/slave/cnf:/etc/mysql/conf.d -v /opt/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=slave123 d435eee2caa5
cbd90a269ffbfa9949c34dcaafc46247d705da8739104cf995803f239906aa01
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cbd90a269ffb d435eee2caa5 "docker-entrypoint.s…" 3 seconds ago Up 2 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp slave
404047f18083 d435eee2caa5 "docker-entrypoint.s…" 6 minutes ago Up 6 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp master
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cbd90a269ffb d435eee2caa5 "docker-entrypoint.s…" 9 seconds ago Up 8 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp slave
404047f18083 d435eee2caa5 "docker-entrypoint.s…" 6 minutes ago Up 6 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp master
[root@VM_0_6_centos mysql]# docker exec -it cbd90a269ffb /bin/bash
root@cbd90a269ffb:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
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> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
root@cbd90a269ffb:/# exit
exit
[root@VM_0_6_centos mysql]#
客户端连接测试:
2、进入主服务器控制台,添加复制master数据的用户replicate,供从服务器使用。
[root@VM_0_6_centos ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cbd90a269ffb d435eee2caa5 "docker-entrypoint.s…" 21 minutes ago Up 21 minutes 33060/tcp, 0.0.0.0:3308->3306/tcp slave
404047f18083 d435eee2caa5 "docker-entrypoint.s…" 27 minutes ago Up 27 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp master
[root@VM_0_6_centos ~]# docker exec -it 404047f18083 /bin/bash
root@404047f18083:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> use mysql
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> CREATE USER 'replicate'@'%' IDENTIFIED BY 'replicate123';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%' identified by 'replicate123';
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 'identified by 'reader123'' at line 1
mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1504 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
上述show master status这个指令结果中的File和Position记录的是当前日志的binlog文件以及文件中的索引。而后面的Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。目前我们没有进行配置,就表示是针对全库记录日志。
开启binlog后,数据库中的所有操作都会被记录到datadir当中,以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时,就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。
注意:在实际生产环境中,通常不会直接使用root用户,而会创建一个拥有全部权限的用户来负责主从同步。
3、在从服务器上配置连接主服务器的信息
[root@VM_0_6_centos ~]# docker exec -it cbd90a269ffb /bin/bash
root@cbd90a269ffb:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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.17.0.6',master_port=3307,master_user='replicate',master_password='replicate123',master_log_file='mysql-bin.000003',master_log_pos=3684;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.17.0.6
Master_User: replicate
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3684
Relay_Log_File: cbd90a269ffb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3684
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3d0dd786-278c-11ea-948f-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.6
Master_User: replicate
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3684
Relay_Log_File: cbd90a269ffb-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3684
Relay_Log_Space: 537
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3d0dd786-278c-11ea-948f-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
说明:
- Slave_IO_Running: Yes,Slave_SQL_Running: Yes即表示启动成功。
- change master to master_host='主服务器Ip',master_port=主服务器端口(不要带引号),master_user='复制用户',master_password='复制用户密码',master_log_file='master状态查看中File',master_log_pos=master状态查看中的Position(不要带引号);
- 如果需要停止slave
stop slave;#如果之前启动过,需要先关闭
reset master;#重置
注意:change master指令中需要指定的Master_Log_File和Read_Master_Log_Pos必须与主服务中查到的保持一致。并且后续如果要检查主从架构是否成功,也可以通过检查主服务与从服
务之间的File和Position这两个属性是否一致来确定。
从这个指令的结果能够看到,有很多Replicate_开头的属性,这些属性指定了两个服务之间要同步哪些数据库、哪些表的配置。只是在我们这个示例中全都没有进行配置,就标识是全库进行同步。
4、通过客户端或服务器上创建测试数据库:tr。
可以看到,tr已成功复制到mysql从服务器(单向复制,即只能从master复制到slaver,而不能逆向进行)。
注意注意注意,重要的事情说三遍:
在这种MySQL主从架构中,是需要严格限制从服务的数据写入的,一旦从服务有数据写入,就会造成数据不一致。并且从服务在执行事务期间还很容易造成数据同步失败。
三、问题
1、mysql 8 中使用 grant ... identified by 时 error 1064 near 'identified by '密码'' at line 1
mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%' identified by 'replicate123';
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 'identified by 'replicate123'' at line 1
解答:新版的的mysql版本已经将创建账户和赋予权限的方式分开了。
创建账户:create user '用户名'@'访问主机' identified by '密码';
赋予权限:grant 权限列表 on 数据库 to '用户名'@'访问主机' ;
2、start slave时候启动后,无法连接主服务器。
Last_IO_Error: error connecting to master 'replicate@172.17.0.6:3307' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
解决:
ALTER USER 'replicate'@'%' IDENTIFIED WITH mysql_native_password BY 'replicate123';
3、执行show slave status\G时,出现:No query specified
出现此错误的原因是因为执行命令:show slave status\G;的时候,多加了一个“;”分号。
4、特别注意
主从架构是有可能失败的,如果在slave从服务上查看slave状态,发现Slave_SQL_Running=no,就表示主从同步失败了。这有可能是因为在从数据库上进行了写操作,与同步过来的SQL操作冲突了,也有可能是slave从服务重启后有事务回滚了。
解决:
1)如果是因为slave从服务事务回滚的原因,可以按照以下方式重启主从同步:
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;
2)另一种解决方式就是重新记录主节点的binlog文件消息。
mysql> stop slave ;
mysql> change master to .....
mysql> start slave ;
但是这种方式要注意binlog的文件和位置,如果修改后和之前的同步接不上,那就会丢失部分数据。所以不推荐使用。