1.主从同步介绍和优点
2.主从同步机制
MySQL服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。
1.获取MySQL镜像
$ sudo docker image pull mysql:5.7.22 或 $ sudo docker load -i 文件路径/mysql_docker_5722.tar
2.指定MySQL从机配置文件
$ cd /data $ mkdir mysql_slave $ cd mysql_slave $ mkdir -p data/mysql.conf.d [root@peiqi data]# ll total 0 drwxr-xr-x. 2 polkitd input 20 Jun 26 16:33 mysql.conf.d [root@peiqi data]# cd mysql.conf.d/ [root@peiqi mysql.conf.d]# pwd /data/mysql_slave/data/mysql.conf.d [root@peiqi mysql.conf.d]# cp /etc/my.cnf ./
3.修改MySQL从机配置文件
[root@peiqi mysql.conf.d]# cat my.cnf [mysqld] bind-address=0.0.0.0 user=mysql basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock pid-file=/data/mysql/mysql.pid #character config character_set_server=utf8mb4 symbolic-links=0 explicit_defaults_for_timestamp=trueg port=8306 # 关闭日志 general_log = 0 #从机唯一编号 server-id = 2
4.Docker安装运行MySQL从机
MYSQL_ROOT_PASSWORD：创建 root 用户的密码为 mysql。docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=root -d --network=host -v /data/mysql_slave/data:/var/lib/mysql -v /data/mysql_slave/data/mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.22
5.测试从机是否创建成功
mysql -uroot -proot -h 127.0.0.1 --port=8306
1.配置主机（ubuntu中MySQL）
sudo service mysql restart[mysqld] bind-address=0.0.0.0 port=3306 user=mysql basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock log-error=/data/mysql/mysql.err pid-file=/data/mysql/mysql.pid #character config character_set_server=utf8mb4 symbolic-links=0 explicit_defaults_for_timestamp=trueg # 开启日志 general_log_file = /var/log/mysql/mysql.log general_log = 1 # 主机唯一编号 server-id = 1 # 二进制日志文件 log_bin = /var/log/mysql/mysql-bin.log
需要给/var/log/mysql 赋予权限
[root@peiqi log]# cd /var/log [root@peiqi log]# chown mysql:mysql -R mysql/ [root@peiqi log]# ll total 472 drwxr-xr-x. 2 root root 204 Jun 26 13:02 anaconda drwx------. 2 root root 23 Jun 26 13:06 audit -rw-------. 1 root root 9334 Jun 26 13:06 boot.log -rw-------. 1 root utmp 0 Jun 26 15:00 btmp -rw-------. 1 root root 2662 Jun 26 17:01 cron -rw-r--r--. 1 root root 125921 Jun 26 13:06 dmesg -rw-r-----. 1 root root 3477 Jun 26 16:05 firewalld -rw-------. 1 root root 1304 Jun 26 15:01 grubby -rw-r--r--. 1 root root 193 Jun 26 12:52 grubby_prune_debug -rw-r--r--. 1 root root 292000 Jun 26 17:13 lastlog -rw-------. 1 root root 190 Jun 26 13:06 maillog -rw-------. 1 root root 240575 Jun 26 17:23 messages drwxr-xr-x. 2 mysql mysql 70 Jun 26 17:05 mysql drwxr-xr-x. 2 root root 6 Jun 26 13:02 rhsm -rw-------. 1 root root 5987 Jun 26 17:13 secure -rw-------. 1 root root 0 Jun 26 12:54 spooler -rw-------. 1 root root 64000 Jun 26 15:07 tallylog drwxr-xr-x. 2 root root 23 Feb 3 00:30 tuned -rw-------. 1 root root 719 Jun 26 13:06 vmware-network.log -rw-------. 1 root root 4044 Jun 26 15:01 vmware-vgauthsvc.log.0 -rw-------. 1 root root 19993 Jun 26 15:01 vmware-vmsvc-root.log -rw-------. 1 root root 522 Jun 26 15:01 vmware-vmtoolsd-root.log -rw-rw-r--. 1 root utmp 3072 Jun 26 17:13 wtmp -rw-------. 1 root root 9055 Jun 26 16:03 yum.log [root@peiqi log]#
2.从机备份主机原有数据
# 1. 收集主机原有数据 $ mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql # 2. 从机复制主机原有数据 $ mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql
3.主从同步实现
# 登录到主机 $ mysql –uroot –p # 创建从机账号 $ GRANT REPLICATION SLAVE ON *.* TO ‘slave‘@‘%‘ identified by ‘slave‘; # 刷新权限 $ FLUSH PRIVILEGES;
$ SHOW MASTER STATUS; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 582 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
# 登录到从机 $ mysql -uroot -proot -h 127.0.0.1 --port=8306 # 从机连接到主机 $ change master to master_host=‘127.0.0.1‘, master_user=‘slave‘, master_password=‘slave‘,master_log_file=‘mysql-bin.000250‘, master_log_pos=990250; # 开启从机服务 $ start slave; # 展示从机服务状态 $ show slave status \G mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 582 Relay_Log_File: peiqi-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: Yes 1 row in set (0.00 sec) mysql>
关于Slave_IO_Running: No 的问题
主节点中查看
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
从节点中查看
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 582 Relay_Log_File: peiqi-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: Yes
解决：
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=0; Query OK, 0 rows affected (0.09 sec) 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: 127.0.0.1 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: peiqi-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
原文：https://www.cnblogs.com/Live-up-to-your-youth/p/14938770.html