MySQL主从
1、MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致有3个步骤：
1）主将更改操作记录到binlog里
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog（中继日志）里
3）从根据relaylog里面的sql语句按顺序执行
2、MySQL主从原理
主服务器上有一个log dump线程，用来和从的I/O线程传递binlog；
从服务器上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。
3、MySQL主从配置
（1）主上操作
[root@antong ~]# vim /etc/my.cnf //修改配置文件以下内容
log_bin = atlinux01
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 10
socket = /tmp/mysql.sock
[root@antong ~]# /etc/init.d/mysqld restart //重启mysqld服务
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@antong bin]# ls /data/mysql/ //生成了两个log_bin文件
antong.err atlinux01.000001 auto.cnf ib_logfile0 mysql test
antong.pid atlinux01.index ibdata1 ib_logfile1 performance_schema
[root@antong ~]# cd /usr/local/mysql/bin/ //切换工作目录，可以自行配置环境变量
[root@antong bin]# ./mysqldump -uroot -p mysql > /tmp/mysql.sql //备份
Enter password: //输入你的密码
[root@antong bin]# ./mysql -uroot -e "create database at" -p //创建测试库
Enter password: //输入密码
[root@antong bin]# ./mysql -uroot -p at < /tmp/mysql.sql //备份的库恢复成新建的库
[root@antong bin]# ./mysql -uroot -p //进入mysql
mysql> grant replication slave on *.* to ‘repl‘ @192.168.200.30 identified by ‘000000‘; //赋予从服务器权限
Query OK, 0 rows affected (0.02 sec)
mysql> flush tables with read lock; //把表锁住
Query OK, 0 rows affected (0.02 sec)
mysql> show master status; //显示主机状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| atlinux01.000002 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库
（2）从上操作
[root@antong ~]# vim /etc/my.cnf //修改配置文件
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 30 //和主的id不能一样
socket = /tmp/mysql.sock
[root@antong ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@antong bin]# scp /tmp/mysql.sql root@192.168.200.30:/tmp/ //将主的备份文件传给从
[root@antong ~]# cd /usr/local/mysql/bin/ //进入bin目录
[root@antong bin]# ./mysql -uroot -p -e "create database at" //创建和主一样的库
[root@antong bin]# ./mysql -uroot -p at < /tmp/mysql.sql //备份和主上的数据一样
Enter password: //输入密码
[root@antong bin]# ./mysql -uroot -p
mysql> stop slave; //关闭slave
mysql> change master to master_host=‘192.168.200.10‘,master_user=‘repl‘,master_password=‘000000‘,master_log_file=‘atlinux01.000002‘,master_log_pos=120; //注意这里pos一定要填主的Position
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave; //启动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: 192.168.200.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: atlinux01.000002
Read_Master_Log_Pos: 120
Relay_Log_File: antong-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: atlinux01.000002
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: 120
Relay_Log_Space: 457
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: 10
Master_UUID: 6c8995bb-e951-11eb-9ef3-000c2994a838
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> unlock tables; //在主上解锁表
Query OK, 0 rows affected (0.01 sec)
replicate_do_db= //仅同步指定的库
replicate_ignore_db= //忽略指定库
replicate_do_table= /仅同步指定的表
replicate_ignore_table= //忽略指定表
replicate_wild_do_table= //某个库的某个表，如test.%, 支持通配符% 可以理解为库.表
replicate_wild_ignore_table=
（3）测试主从
主服务器上：
[root@antong bin]# ./mysql -uroot -p
mysql> use at;
Database changed
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
从服务器上：
[root@antong bin]# ./mysql -uroot -p
mysql> use at;
Database changed
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
在主服务器上删除表内容：
mysql> truncate table db;
Query OK, 0 rows affected (0.04 sec)
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
在从服务器上查看：
mysql> select count(*) from db; //查看后也被删掉了，说明同步成功
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)