# 环境配置 |ID|IP|服务| |-------|-------|-------| |db01| 192.168.200.65| MySQL-master| |db02 |192.168.200.66 |MySQL-slave db03 |192.168.200.67| MySQL-slave| # 修改主库和从库配置文件并重启 Mater主库配置文件 ```bash [root@db01 ~]# cat /etc/my.cnf|grep -v "#" [mysqld] basedir=/application/mysql datadir=/application/mysql/data port=3306 socket=/application/mysql/tmp/mysql.sock log-bin=/application/mysql/data/mysql-bin #打开主库binlog，可自定义路径 server-id=1 binlog_format=MIXED #开启select也会记录 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` Slave从库配置文件 两Slave从库依次配置，server-id为2,3 ```bash [root@db02 data]# cat /etc/my.cnf|grep -v "#" [mysqld] basedir=/application/mysql datadir=/application/mysql/data port=3306 log-bin=/application/mysql/data/mysql-bin #可选择开启或者关闭，不联级的话 server-id=2 #id不能相同 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` # 查看主库配置是否生效 ```bash mysql> show variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 2767 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` # 在主库创建复制用户rep ```bash mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +------+---------------+ | user | host | +------+---------------+ | dev | % | | root | % | | rep | 192.168.200.% | +------+---------------+ 3 rows in set (0.00 sec) ``` # 主库备份文件导入从库 ```bash mysql> flush table with read lock; # 在主库上给所有表加只读锁 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #备份数据 [root@db01 ~]# mysqldump -uroot -p123456 -A -B --events >/opt/rep.sql [root@db01 ~]# mysqldump -uroot -p123456 -A -B --events --master-data=2 >/opt/rep.sql #拷贝至从库还原 [root@db01 ~]# rsync -av /opt/rep.sql root@192.168.200.66:/root/ [root@db01 ~]# rsync -av /opt/rep.sql root@192.168.200.66:/root/ [root@db02 data]# mysql -uroot -p123456 < /root/rep.sql [root@db03 data]# mysql -uroot -p123456 < /root/rep.sql #打开只读锁 mysql> unlock tables; ``` # 两从库配置连接主库信息 ```bash #其中host为主库地址 user为上述创建复制用户 MASTER_LOG_FILE和POS主库述查看为准 mysql> CHANGE MASTER TO MASTER_HOST = '192.168.200.65', MASTER_USER = 'rep', MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2767; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) #执行完毕后相应目录下生成master.info文件 [root@db03 ~]# cat /application/mysql/data/master.info 23 mysql-bin.000003 2767 192.168.200.65 rep 123456 3306 ...... ``` # 打开从库两个线程 ```bash 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: 192.168.200.65 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 120 Relay_Log_File: db02-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes #遇到问题两个线程中有一个是NO；是因为server_id相同或者是uuid相同，修改一下就好了，auto.conf文件需删除，根据错误日志检查 [root@db02 data]# ll /application/mysql/data/ 总用量 110664 -rw-rw----. 1 mysql mysql 56 12月 31 11:29 auto.cnf -rw-rw----. 1 mysql mysql 10780 12月 31 11:50 db02.err -rw-rw----. 1 mysql mysql 6 1月 3 14:18 db02.pid -rw-rw----. 1 mysql mysql 172 1月 3 14:22 db02-relay-bin.000001 -rw-rw----. 1 mysql mysql 283 1月 3 14:22 db02-relay-bin.000002 -rw-rw----. 1 mysql mysql 48 1月 3 14:22 db02-relay-bin.index -rw-rw----. 1 mysql mysql 6 12月 31 15:09 db04.pid -rw-rw----. 1 mysql mysql 12582912 1月 3 14:18 ibdata1 -rw-rw----. 1 mysql mysql 50331648 1月 3 14:18 ib_logfile0 -rw-rw----. 1 mysql mysql 50331648 12月 31 11:27 ib_logfile1 -rw-rw----. 1 mysql mysql 126 1月 3 14:22 master.info drwx------. 2 mysql mysql 8192 12月 31 11:27 mysql -rw-rw----. 1 mysql mysql 199 1月 3 14:22 mysql-bin.000001 -rw-rw----. 1 mysql mysql 41 1月 3 14:18 mysql-bin.index drwx------. 2 mysql mysql 4096 12月 31 11:27 performance_schema -rw-rw----. 1 mysql mysql 57 1月 3 14:22 relay-log.info drwxr-xr-x. 2 mysql mysql 20 12月 31 11:22 test ``` # 检查主从复制是否一致 ```bash #主库db01添加新表 mysql> create database kiwi; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kiwi | | lw | | mysql | | oldboy | | oldgril | | ops | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) #从库db02查看是否复制 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kiwi | | lw | | mysql | | oldboy | | oldgril | | ops | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) ##从库db03查看是否复制 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kiwi | | lw | | mysql | | oldboy | | oldgril | | ops | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) ```