17.1 MySQL主从介绍

Mysql主从又叫Replication,AB复制。A、B两台机器做主从后，A上写数据，B上也会跟着写数据，A和B的数据实时同步。

Mysql的主从实现是基于binlog的，要开启binlog才能够实现主从功能。

Mysql主从的原理：3个步骤

1、主将更改操作记录到binlog里。2、从将主的binlog事件（sql语句）同步到从的机器上，并记录在relaylog里（中继日志）。3、根据relaylog里的sql语句，按顺序执行。主上有一个logdump线程，用来与从的I/O线程传递binlog。从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另一个线程是SQL线程把relaylog的SQL语句执行落地。

使用场景：

1、单独的数据备份：主机器宕机后，备份的从机器可以给Web服务提供数据2、读备份库：web服务从主上读写数据，当主机器访问压力大的时候，可以在从上读数据，缓解主压力，但是从机器不写入数据。

17.2 准备工作

主机器A、从机器B上都安装Mysql：二进制免编译包安装

1、解压，初始化mysql：

[root@lgs-02 src]# tar zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gzmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/oracle.cfgmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/mysql.cfgmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/Adabas.cfgmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/empress.cfgmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/mysql-4.0.cfgmysql-5.6.36-linux-glibc2.5-x86_64/sql-bench/limits/interbase.cfg[root@lgs-02 src]# lsmysql-5.6.36-linux-glibc2.5-x86_64  mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz[root@lgs-02 mysql]# ./scripts/mysql_install_db  --user=mysql --datadir=/data/mysql/Installing MySQL system tables...2018-05-23 18:44:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2018-05-23 18:44:50 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.2018-05-23 18:44:50 0 [Note] ./bin/mysqld (mysqld 5.6.36) starting as process 1462 ...2018-05-23 18:44:50 1462 [Note] InnoDB: Using atomics to ref count buffer pool pages2018-05-23 18:45:07 1462 [Note] InnoDB: Shutdown completed; log sequence number 1625977OKFilling help tables...2018-05-23 18:45:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2018-05-23 18:45:07 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.2018-05-23 18:45:07 0 [Note] ./bin/mysqld (mysqld 5.6.36) starting as process 1484 ...2018-05-23 18:45:07 1484 [Note] InnoDB: Using atomics to ref count buffer pool pages2018-05-23 18:45:07 1484 [Note] InnoDB: The InnoDB memory heap is disabled2018-05-23 18:45:07 1484 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-05-23 18:45:07 1484 [Note] InnoDB: Memory barrier is not used2018-05-23 18:45:07 1484 [Note] InnoDB: Compressed tables use zlib 1.2.32018-05-23 18:45:07 1484 [Note] InnoDB: Using Linux native AIO2018-05-23 18:45:07 1484 [Note] InnoDB: Using CPU crc32 instructions2018-05-23 18:45:07 1484 [Note] InnoDB: Initializing buffer pool, size = 128.0M2018-05-23 18:45:07 1484 [Note] InnoDB: Completed initialization of buffer pool2018-05-23 18:45:07 1484 [Note] InnoDB: Highest supported file format is Barracuda.2018-05-23 18:45:07 1484 [Note] InnoDB: 128 rollback segment(s) are active.2018-05-23 18:45:07 1484 [Note] InnoDB: Waiting for purge to start2018-05-23 18:45:07 1484 [Note] InnoDB: 5.6.36 started; log sequence number 16259772018-05-23 18:45:07 1484 [Note] Binlog end2018-05-23 18:45:07 1484 [Note] InnoDB: FTS optimize thread exiting.2018-05-23 18:45:07 1484 [Note] InnoDB: Starting shutdown...2018-05-23 18:45:09 1484 [Note] InnoDB: Shutdown completed; log sequence number 1625987OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:  ./bin/mysqladmin -u root password 'new-password'  ./bin/mysqladmin -u root -h lgs-02 password 'new-password'

2、启动mysqld服务，启动前：修改my.cnf配置文件的basedir、datadir，拷贝启动脚本（也要改脚本的basedir、datadir）

[root@lgs-02 mysql]# ls support-files/my-default.cnf support-files/my-default.cnf[root@lgs-02 mysql]# cp support-files/my-default.cnf /etc/my.cnfcp：是否覆盖"/etc/my.cnf"？ y[root@lgs-02 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@lgs-02 ~]# ls -l /etc/init.d/mysqld -rwxr-xr-x. 1 root root 10565 5月  23 19:23 /etc/init.d/mysqld[root@lgs-02 ~]# vim !$vim /etc/init.d/mysqldbasedir=/usr/local/mysql/datadir=/data/mysql/[root@lgs-02 ~]# /etc/init.d/mysqld startStarting MySQL.Logging to '/data/mysql/lgs-02.err'............. SUCCESS!

17.3 配置主

1、修改my.cnf：设置服务id、定义binlog日志名字，重启mysqld

[root@lgs local]# vim /etc/my.cnflog_bin=lgs01    //binlog日志文件的前缀，在/data/mysql/目录下生成 lgs01.index和lgs01.000001server_id =130[root@lgs local]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

2、创建一个库，恢复填充数据进去用作实验：zc01库

[root@lgs tmp]# mysqldump -uroot -p7826078 zrlog >/tmp/blog.sql   //备份zrlog库到 /tmp/目录下Warning: Using a password on the command line interface can be insecure.[root@lgs tmp]# mysql -uroot -p7826078 -e "create database zc01"  //创建zc01的库，用作实验Warning: Using a password on the command line interface can be insecure.[root@lgs tmp]# mysql -uroot -p7826078 zc01  use zc01;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_zc01 |+----------------+| comment        || link           || log            || lognav         || plugin         || tag            || type           || user           || website        |+----------------+9 rows in set (0.00 sec)

3、创建同步数据的用户:repl

mysql> grant replication slave on *.* to 'repl'@'192.168.87.132' identified by '7826078';  //指定来源ip的从机器的ip。Query OK, 0 rows affected (0.00 sec)mysql> flush tables with read lock;  //锁表，不让继续写数据Query OK, 0 rows affected (0.02 sec)mysql> show master status;   //记住file和Position的内容，从机器配置的时候要用到。+--------------+----------+--------------+------------------+-------------------+| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+--------------+----------+--------------+------------------+-------------------+| lgs01.000001 |    35637 |              |                  |                   |+--------------+----------+--------------+------------------+-------------------+1 row in set (0.01 sec)[root@lgs tmp]# ls -l /data/mysql/   //主机器上的库文件，一会同步zc01库。总用量 110820-rw-rw----. 1 mysql mysql       56 4月  17 18:58 auto.cnf-rw-rw----. 1 mysql mysql 12582912 6月  28 23:27 ibdata1-rw-rw----. 1 mysql mysql 50331648 6月  28 23:27 ib_logfile0-rw-rw----. 1 mysql mysql 50331648 4月  17 18:47 ib_logfile1-rw-rw----  1 mysql mysql    35637 6月  28 23:43 lgs01.000001-rw-rw----. 1 mysql mysql   130455 5月  10 08:12 lgs-01.err-rw-rw----  1 mysql mysql       15 6月  28 22:16 lgs01.index-rw-rw----  1 mysql mysql    43229 6月  28 22:16 lgs.err-rw-rw----  1 mysql mysql        5 6月  28 22:16 lgs.piddrwx------. 2 mysql mysql     4096 4月  17 18:47 mysqldrwx------. 2 mysql mysql     4096 4月  17 18:47 performance_schemadrwx------. 2 mysql mysql        6 4月  17 18:47 testdrwx------  2 mysql mysql      324 6月  28 23:27 zc01

4、备份zc01库文件，拷贝到从机器上去：在从上新建库，用zc01库文件数据恢复到新建的库中去。

[root@lgs tmp]# mysqldump -uroot -p7826078 zc01 >/tmp/zc01.sqlWarning: Using a password on the command line interface can be insecure.[root@lgs tmp]# ls -l zc01.sql -rw-r--r-- 1 root root 34508 6月  28 23:51 zc01.sql

17.4 配置从

1、修改my.cnf文件：设置服务id，与主的服务id不一样。重启mysqld

[root@lgs-02 tmp]# vim /etc/my.cnf[mysqld]server_id =131[root@lgs-02 tmp]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

2、把主上的zc01.sql库文件拷贝到从上：放/tmp/目录下

3、创建一个库，把zc01的数据恢复进去

mysql> create database zc01;   //创建新库zc01Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               || zc01               |+--------------------+5 rows in set (0.01 sec)mysql> quitBye[root@lgs-02 tmp]# mysql -uroot -p7826078 zc01 

4、实现主从：

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host='192.168.87.130',master_user='repl',master_password='7826078',master_log_file='lgs01.000001',master_log_pos=35637;  //配置主信息Query OK, 0 rows affected, 2 warnings (0.05 sec)mysql> start slave;  //打开主从同步Query OK, 0 rows affected (0.02 sec)

5、检测是否成功配置主从：Slave_IO_Running: Yes和Slave_SQL_Running: Yes 就代表成功配置

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.87.130                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: lgs01.000001          Read_Master_Log_Pos: 35637               Relay_Log_File: lgs-02-relay-bin.000003                Relay_Log_Pos: 279        Relay_Master_Log_File: lgs01.000001             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: 35637              Relay_Log_Space: 612              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: 0Master_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: 130                  Master_UUID: 40e65727-422e-11e8-8d14-000c29d0e2b8             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: 01 row in set (0.00 sec)

如果Slave_IO_Running: Connectting. 把主上的firewalld、selinux关掉再打开同步。

6、主机器上：解锁表。

mysql> unlock tables;Query OK, 0 rows affected (0.01 sec)

7、关注这些主从的状态：

Seconds_Behind_Master: 0  //延时Last_IO_Errno: 0  //io线程错误Last_IO_Error: Last_SQL_Errno: 0   //执行sql错误Last_SQL_Error:

17.5 测试主从同步

主机器上写入数据，看能否同步到从机器上。

测试前，可以自定义几个主从相关的参数:my.cnf

## 主机器上的配置[root@lgs tmp]# vim /etc/my.cnf[mysqld]binlog_do_db=        //仅同步指定的库，多个库用逗号分隔binlog_ignore_db=    //忽略指定库，剩余的都同步。

##从机器上的配置Replicate_do_db=    Replicate_ignore_db=Replicate_do_table=Replicate_ignore_table=Replicate_wild_do_table=Replicate_wild_ignore_table=

开始测试：

##主上mysql> use zc01;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_zc01 |+----------------+| comment        || link           || log            || lognav         || plugin         || tag            || type           || user           || website        |+----------------+9 rows in set (0.00 sec)mysql> select count(*) user;+------+| user |+------+|    1 |+------+1 row in set (0.01 sec)mysql> select count(*) comment;+---------+| comment |+---------+|       1 |+---------+1 row in set (0.00 sec)##从上mysql> use zc01;Database changedmysql> show tables;+----------------+| Tables_in_zc01 |+----------------+| comment        || link           || log            || lognav         || plugin         || tag            || type           || user           || website        |+----------------+9 rows in set (0.00 sec)mysql> select count(*) user;+------+| user |+------+|    1 |+------+1 row in set (0.01 sec)mysql> select count(*) comment;+---------+| comment |+---------+|       1 |+---------+1 row in set (0.00 sec)mysql> select count(*) website;+---------+| website |+---------+|       1 |+---------+1 row in set (0.01 sec)

主上删除comment表的数据：再看看从的comment表是否也会0行

mysql> drop table comment;Query OK, 0 rows affected (0.02 sec)mysql> show tables;+----------------+| Tables_in_zc01 |+----------------+| link           || log            || lognav         || plugin         || tag            || type           || user           || website        |+----------------+8 rows in set (0.00 sec)

从上查询comment表的时候就报错不存在表了：证明从上同步主的操作了。

mysql> select * from comment;ERROR 1146 (42S02): Table 'zc01.comment' doesn't exist