本文索引：

• MySQL主从介绍
• 准备工作
• 配置主
• 配置从
• 测试主从同步

MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单将就是A/B两个服务器做主从后，在A上写数据，B也会跟着写数据，两者数据是实时同步的。

MySQL主从是基于binlog的，主服务器需要开启binlog才能进行主从配置。

主从配置大致有3个步骤：

1. 主服务器将更改操作记录到binlog里；
2. 从服务器将主服务器的binlog事件（sql语句）同步到本机并极力到relaylog里；
3. 从服务器根据relaylog里面的sql语句按顺序执行；

主服务器上有一个log dump线程，用来和从服务器的I/O线程传递binlog。

从服务器上有两个线程，其中I/O线程用来同步主服务器的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。

应用场景：

• 数据备份，在主服务器出现故障时，从服务器代替主服务器提供读取服务；
• 备份的同时web服务器会到从服务器上读取数据，减轻主服务器的数据读取压力；

MySQL安装

在测试主从的2台服务器上都安装上MySQL，具体操作步骤如下：

1# 2台服务器配置相同，这里只写出其中一台
2[root@master src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
3[root@master src]# tar zxf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz 
4[root@master src]# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql
5[root@master src]# cd /usr/local/mysql/
6[root@master mysql]# useradd mysql
7[root@master mysql]# mkdir /data
8[root@master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
9[root@master mysql]# cp support-files/my-default.cnf /etc/my.cnf
10cp：是否覆盖"/etc/my.cnf"？ y
11# 修改配置文件内basedir和datadir参数
12[root@master mysql]# vi /etc/my.cnf
13修改[mysqld]内的2行即可
14 basedir = /usr/local/mysql
15 datadir = /data/mysql
16保存退出
17
18[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld
19# 修改mysqld配置文件，
20[root@master mysql]# vi /etc/init.d/mysqld 
21同样要修改一下参数
22basedir=/usr/local/mysql
23datadir=/data/mysql
24
25[root@master mysql]# chmod 755 /etc/init.d/mysqld 
26[root@master mysql]# chkconfig --add mysqld

• 启动MySQL

  [root@master mysql]# /etc/init.d/mysqld start Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'. .. SUCCESS!

  [root@master mysql]# ps aux | grep mysqld root 2856 0.0 0.1 113264 1616 pts/0 S 13:52 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid mysql 2990 8.5 45.0 1308984 450220 pts/0 Sl 13:52 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/localhost.localdomain.err --pid-file=/data/mysql/localhost.localdomain.pid root 3019 0.0 0.0 112676 972 pts/0 R+ 13:53 0:00 grep --color=auto mysqld

配置主服务器：192.168.65.133

log_bin参数只在主服务器上设置

修改my.cnf，增加server_id=133，log_bin=test1，修改后重启MySQL

会在/data/mysql目录下生成以test1前缀的多个文件

1[root@master ~]# mysqldump -uroot -p1 test > /tmp/test.sql
2[root@master ~]# mysql -uroot -p1 -e "create database test1"
3[root@master ~]# mysql -uroot -p1 test1 < /tmp/test.sql 
4
5[root@master ~]# ls -l /data/mysql/test1.*
6-rw-rw----. 1 mysql mysql 425 1月  15 19:39 /data/mysql/test1.000001
7-rw-rw----. 1 mysql mysql  15 1月  15 19:36 /data/mysql/test1.index

创建用作同步数据的用户

1mysql> grant replication slave on *.* to 'repl'@'192.168.65.134' identified by 'test2';
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> flush tables with read lock;
5Query OK, 0 rows affected (0.05 sec)
6
7mysql> show master status;
8+--------------+----------+--------------+------------------+-------------------+
9| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
10+--------------+----------+--------------+------------------+-------------------+
11| test1.000001 |      636 |              |                  |                   |
12+--------------+----------+--------------+------------------+-------------------+
131 row in set (0.00 sec)

可选操作：

1将/data/mysql下的除mysql外的库都进行备份(如果有其他库的话)
2例如：mysqldump -uroot -p1 zrlog > /tmp/zrlog.sql

在配置时最好也放开mysql通信端口3306，防止主从无法通信。

配置从服务器：192.168.65.134

修改my.cnf，增加server_id=134。重启服务

1[root@backup ~]# vi /usr/local/mysql/my.cnf 
2[root@backup ~]# /etc/init.d/mysqld restart
3Shutting down MySQL.. SUCCESS! 
4Starting MySQL. SUCCESS! 

同步备份的数据库

1[root@backup ~]# scp 192.168.65.133:/tmp/*.sql /tmp
2The authenticity of host '192.168.65.133 (192.168.65.133)' can't be established.
3ECDSA key fingerprint is 42:50:a7:09:91:db:af:77:a5:3a:b3:67:1c:8a:5b:99.
4Are you sure you want to continue connecting (yes/no)? yes
5Warning: Permanently added '192.168.65.133' (ECDSA) to the list of known hosts.
6root@192.168.65.133's password: 
7test.sql                   100% 1258     1.2KB/s   00:00  

创建主服务器同名用户

1[root@backup ~]# mysql -uroot -p1
2
3mysql> create database test;
4Query OK, 1 row affected (0.01 sec)

恢复数据

1# 同步过来的有多少个数据库就做多少个恢复
2[root@backup ~]# mysql -uroot -p1 test < /tmp/test.sql 

登录mysql，执行从配置

1[root@backup ~]# mysql -uroot -p1
2//master_log_file填主服务器show master status;显示的file内容  
3//master_log_pos填主服务器show master status;显示的position内容
4mysql> stop slave;
5Query OK, 0 rows affected, 1 warning (0.00 sec)
6
7mysql> change master to master_host='192.168.65.133', maste_user='repl', master_password='test2', master_log_file='test1.000001', master_log_pos=636;
8Query OK, 0 rows affected, 2 warnings (0.05 sec)
9
10mysql> start slave;
11Query OK, 0 rows affected (0.00 sec)

查看是否连接成功

1mysql> show slave status\G
2*************************** 1. row ***************************
3              Slave_IO_State: Connecting to master
4                 Master_Host: 192.168.65.133
5                 Master_User: repl
6                 Master_Port: 3306
7               Connect_Retry: 60
8             Master_Log_File: test1.000001
9         Read_Master_Log_Pos: 636
10              Relay_Log_File: server-relay-bin.000001
11               Relay_Log_Pos: 4
12       Relay_Master_Log_File: test1.000001
13            Slave_IO_Running: Yes
14           Slave_SQL_Running: Yes
15           ...
16           
17下列2行都为Yes即成功连接
18Slave_IO_Running: Yes
19Slave_SQL_Running: Yes

完成后到主服务器上执行解锁命令

mysql> unlock tables;

测试主从同步

常用参数分析

主服务器上

1binlog-do-db=   //仅同步主服务器上指定的库，多个库用逗号分隔
2binlog-ignore-db=   //忽略指定库

从服务器上

1指定同步从服务器上的库、表
2replicate_do_db=
3replicate_ignore_db=
4replicate_do_table=
5replicate_ignore_table=
6replicate_wild_do_table=    //支持通配符%，例如test.%（库.表）
7replicate_wild_ignore_table=

测试

1. 创建操作

   主服务器上新建一个db库

   mysql> create database db; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

   #从上也创建了一个数据库db mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

2. 删除操作

   主服务器上

   删除表

   mysql> drop table wp_user;

   从服务器上

   从上的表也被删了

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

如果在从服务器上先误删了数据库，主服务器上再去删时，从服务器将会报错，连接会down掉。

在数据一致的前提下重连主从：

1主上：
2mysql> show master status\G
3
4从上：
5mysql> stop slave;
6mysql> change master to master_host='192.168.65.133', master_user='repl', master_password='test2', master_log_file='', master_log_pos=新id;
7mysql> start slave;

数据不一致： 需要重新配置主从：先将主服务器上的数据库重新备份，再在从服务器上重新配置。