MySQL主从复制

主库

• 开启主库binlog和server_id

vim /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/mysql-bin
server_id=1
systemctl restart mysql

• 添加同步账号

mysql> grant replication slave on *.* to "rep"@"172.16.100.%" identified by "000000";
mysql> show master status;   
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |     1077 |              |                  |
+------------------+----------+--------------+------------------+

• 数据库完整打包备份拷贝至丛库

mysqldump -uroot -p -ABREx --master-data=2| gzip > master.sql.gz
scp master.sql.gz 172.16.100.20:/root

丛库

• 设置server_id

vim /etc/my.cnf
server_id=2

• 导入主库数据库

gzip -d master.sql.gz
mysql -uroot -p < master.sql

• 生成master.info文件

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1077, MASTER_HOST='172.16.100.10', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='000000';

• 开启slave并检查

mysql> start slave;
mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  8 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | show processlist |
| 12 | system user |           | NULL | Connect |  363 | Waiting for master to send event                                            | NULL             |
| 13 | system user |           | NULL | Connect |  363 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

这里可以写一个监控slave脚本

RES=mysql -uzabbix -pzabbix00 -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
if [ "$RES" = " Yes, Yes," ]; then
echo 1
else
echo 0
fi
exit