MySQL主从

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)