mysql-5.6.24主从复制

htgefwda

一、mysql主从备份（复制）的基本原理   
   mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

二、mysql主从备份配置方法（作为主从服务器的mysql版本建议使用同一版本！或者必须保证主服务器的mysql版本要高于从服务器的mysql版本）

   在进行mysql主从备份时，最好确保主从服务器的版本兼容。从服务器至少与主服务器版本相同或更高。
1、配置防火墙，开启3306端口

1 2 3

vi /etc/sysconfig/iptables  #编辑iptables文件，添加下面语句 iptables -A OUTPUT -p tcp --sport 3306 -j ACCEPT #开启防火墙3306端口 /etc/init.d/iptables restart #重启防火墙使配置生效（ps：也可以这样重启service iptables                                restart）

2、关闭selinux

1 2 3 4 5 6

vi /etc/selinux/config #SELINUX=enforcing #注释掉 #SELINUXTYPE=targeted #注释掉 SELINUX=disabled #添加 setenforce 0 #使selinux立即生效

3、配置mysql主服务器（172.16.0.110）

1 2 3 4 5 6 7

mysql -u root -p #进入主mysql控制台 mysql> create database osyunweidb; #建立数据库magedudb mysql> create user 'magedu'@'%' identified by '123456';#创建mysql用户名为：magedu，%表示                                                         任意远程主机登陆，密码为：123456 mysql> grant replication slave  on *.* to 'magedu'@'172.16.0.104' identified by '123456' with grant option; #授权用户magedu从172.16.0.104完全访问数据库，注意：这里的172.16.0.104                     是要连接数据库Web服务器IP（ps：因为测试其他机子，如果只是学习安装的话                    ，这步可以跳过） mysql> create user 'magedu.com'@'%' identified by '123456'#创建mysql主从数据库同步用户mag                                                           edu.com密码123456 mysql> flush privileges; #刷新系统授权表 mysql> grant replication slave  on *.* to 'magedu.com'@'172.16.0.104' identified by '123456' with grant option;  #授权用户magedu.com只能从172.16.0.104这个IP访问主服务器                                   172.16.0.110上面的数据库，并且只具有数据库备份的权限

4、把mysql主服务器172.16.0.110中的数据库magedudb导入到mysql从服务器172.16.0.104中

1 2 3 4 5 6

touch /home/magedu.com.sql #创建magedu.com.sql文件 在导出数据库之前可以先进入主mysql控制台执行下面命令 mysql> flush tables with read lock; #数据库只读锁定命令，防止导出数据库的时候有数据写入 mysql> unlock tables; #解除锁定 mysqldump -u root -p --default-character-set=utf8 --opt -Q -R --skip-lock-tables magedud b > /home/magedu.com.sql #把主服务器上的数据库magedudb的数据导入主服务器上的/home/magedu.                          com.sql（ps：需要输入主服务器的mysql密码） scp /home/osyunweidbbak.sql root@172.16.0.104:/home #把主服务器的/home/magedu.com.sql                                                         文件上传到从服务器下/home（ps：需要                                                      输入从服务器root的密码）

5、导入数据库到mysql从服务器

1 2 3 4 5

mysql -u root -p #进入从服务器mysql控制台 mysql> create database magedudb; #创建数据库 mysql> use magedudb #进入magedudb数据库 source /home/magedu.com.sql #导入备份文件到数据库 mysql -u magedu.com -h 172.16.0.110 -p #测试在从服务器上登录到主服务器（ps：需要输入主服                                         务器root的密码）

6、修改主机（master）mysql配置文件my.cnf
在[mysqld]标签下添加以下几行

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

server-id=1 #设置服务器id，为1表示主服务器，此为写1为默认 log-bin=mysql-bin #启动mysql二进制日志系统                   binlog-do-db=magedudb #需要同步的数据库名，如果有多个数据库，可重复此参数 binlog-ignore-db=mysql #不同步mysql系统数据库 service mysqld  restart #重启主服务器mysql mysql -u root -p #进入主mysql控制台 mysql> show variables like 'server_id'; #查看主服务器ID，默认为1 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id     | 1     | +---------------+-------+ 1 row in set (0.01 sec)

7、show master status; #查看主服务器，出现以下类似信息

1 2 3 4 5 6 7

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000006 |      412 | magedudb     | mysql            |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

注意：这里记住File和Position的值：mysql-bin.000006和Position的值：412，后面会用到。


8、修改从机（slave）mysql配置文件my.cnf
在[mysqld]标签下添加以下几行

1 2 3 4 5 6 7

server-id=2 #设置服务器id，为2表示从服务器，此为写2为默认 log-bin=mysql-bin #启动MySQ二进制日志系统，                   binlog-do-db=magedudb #需要同步的数据库名，如果有多个数据库，可重复此参数， binlog-ignore-db=mysql #不同步mysql系统数据库 read_only #设置数据库只读 service mysqld restart #重启mysql

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

mysql -u root -p #进入从mysql控制台 mysql> show variables like 'server_id'; #查看从服务器ID，默认为2，注意如未有设置，必须为上面设置                                  的2，否则请返回修改配置文件 mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id     | 2     | +---------------+-------+ 1 row in set (0.01 sec) mysql> stop slave; #停止slave同步进程 mysql> change master to master_host='172.16.0.110',master_user='magedu.com',master_password='123456',master_log_file='mysql-bin.000006' ,master_log_pos=412; #执行同步语句 mysql> start slave; #开启slave同步进程 show slave status\g #查看slave同步信息，出现以下内容 mysql> show slave status\g *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.0.110                   Master_User: magedu.com                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000006           Read_Master_Log_Pos: 412                Relay_Log_File: DS-CentOS107-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000006              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: magedudb           Replicate_Ignore_DB: mysql            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: 463               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: 1                   Master_UUID: 770a7eec-bac6-11e5-8f74-000c29f38a66              Master_Info_File: /usr/local/data/mysql/data/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)

注意查看：Slave_IO_Running: Yes     Slave_SQL_Running: Yes  #这两个参数的值为Yes，即说明配置成功！


9、测试mysql主从服务器是否正常运行
① 进入主mysql服务器（172.16.0.110）

1 2 3

mysql -u root -p #进入主mysql控制台 mysql> use magedudb #进入数据库，只能在这测试，前期只设置该数据库同步，如需要其他数据库同步，                 请在mysql的配置文件再次设置 mysql> create table test ( id int not null primary key,name char(20) ); #创建test表

② 进入从mysql服务器（172.16.0.104）

1 2 3 4 5 6 7 8 9 10 11

mysql -u root -p #进入从mysql控制台 mysql> use magedudb #进入数据库 mysql> show tables; #查看magedudb表结构，会看到有一个新建的表test，表示数据库同步成功 mysql> show  tables; +----------------------+ | Tables_in_magedudb   | +----------------------+ | test                 | +----------------------+ 1 row in set (0.00 sec)