Mysql主从复制

一、主从复制原理：

将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。简单的说：从服务器的IO线程从主服务器获取二进制日志，并在本地保存为中继日志，然后通过SQL线程来在从上执行中继日志中的内容，从而使从库和主库保持一致。

A.master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

B.slave将master的binary log events拷贝到它的中继日志(relay log)；

C.slave重做中继日志中的事件，将改变反映它自己的数据。

1、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容； 

2、Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

3、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“需要从bin-log的哪个位置开始往后的日志内容，然后给Slave”；

4、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

5、mysql的 replication 是一个异步的复制过程，从master复制到slave。在 master 与 slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 slave 端，另外一个线程(IO线程)在 master端。

6、关键点：负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

二、环境描述：

Master的IP：192.168.44.151

Slave的IP：192.168.44.152

三、主服务器配置：

1、修改配置文件，添加如下内容

vi /etc/my.cnf 

server-id=1

log-bin=mysql-bin

relay-log=mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2、重启服务，使配置文件生效

/etc/init.d/mysqld restart 

3、登录主服务器

mysql -uroot -p

4、创建同步用户bob，授权同步权限给从服务器192.168.44.152

mysql> create user ‘bob‘@‘192.168.44.152‘ identified by ‘123456‘; 

mysql>grant replication slave on *.* to ‘bob‘@‘192.168.44.152‘ identified by ‘123456‘;   

5、查看主服务器状态

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      106 |              | MySQL            | 

+------------------+----------+--------------+------------------+

四、从服务器配置：

1、修改配置文件，添加如下内容

vi /etc/my.cnf

server-id=2

log-bin=mysql-bin

relay-log=mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2、重启服务，使配置文件生效

/etc/init.d/mysqld restart

3、配置从服务器

mysql> change master to master_host=‘192.168.44.151‘,master_user=‘bob‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=106;

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first //报错了

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host=‘192.168.44.151‘,master_user=‘bob‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=106;

Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: 

                  Master_Host: 192.168.44.151

                  Master_User: bob

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 106

               Relay_Log_File: B-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB: test_1

          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: 106

              Relay_Log_Space: 106

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1130

                Last_IO_Error: error connecting to master ‘[email protected]:3306‘ - retry-time: 60  retries: 86400

               Last_SQL_Errno: 0

               Last_SQL_Error: 

1 row in set (0.00 sec)

4、启动从服务器复制功能 

mysql> start 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.44.151

                  Master_User: bob

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 106

               Relay_Log_File: B-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: test_1

          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: 106

              Relay_Log_Space: 402

              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: 

1 row in set (0.00 sec)

五、主从同步检验：

主服务器创建库test_1，从服务器也会创建test_1

mysql> create database test_1;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| mysql              | 

| test               | 

| test_1             | 

+--------------------+

4 rows in set (0.00 sec)

mysql> use test_1;

Database changed

mysql>  create table bob(id int(1),name char(5));

Query OK, 0 rows affected (0.10 sec)

mysql>  insert into bob values (1,‘bob‘);

Query OK, 1 row affected (0.05 sec)

mysql> select * from bob;

+------+------+

| id   | name |

+------+------+

|    1 | bob  | 

+------+------+

1 row in set (0.00 sec)

六、主从不同步解决办法

先到Master上看： 

mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。 

show master status; 正常。 

mysql> show master status; 

再到Slave上查看 ：

mysql> show slave status\G 

Slave_IO_Running: Yes 

Slave_SQL_Running: No 

可见是Slave不同步 

方法一：忽略错误后，继续同步 

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况 

mysql>stop slave; 

skip表示跳过一步错误，后面的数字可变 

mysql>set global sql_slave_skip_counter =1; 

mysql>start slave; 

mysql> show slave status\G  

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes 

方法二：完全同步，该方法适用于主从库数据相差较大，或者要求数据完全统一的情况 

1、先进入master进行锁表，防止数据写入 

mysql> flush tables with read lock; 

2、然后将数据导出，进行数据备份 

[root@mysql]#mysqldump -uroot -p -hlocalhost > mysqlbak.sql 

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失 

3、查看master 状态 

mysql> show master status; 

+-------------------+----------+--------------+-------------------------------+ 

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

+-------------------+----------+--------------+-------------------------------+ 

| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 

+-------------------+----------+--------------+-------------------------------+ 

1 row in set (0.00 sec)

4、把mysql备份文件传到从库机器，进行数据恢复 

[root@mysql]# scp mysqlbak.sql [email protected]:/tmp/ 

5、停止从库的状态 

mysql> stop slave; 

6、然后到slave执行mysql命令，导入数据备份 

mysql> source /tmp/mysqlbak.sql 

7、设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项 

change master to master_host = ‘192.168.128.100‘, master_user = ‘rsync‘, master_port=3306, master_password=‘‘, master_log_file = ‘mysqld-bin.000001‘, master_log_pos=3260; 

8、重新开启从同步 

mysql> start slave; 

9、查看同步状态 

mysql> show slave status\G  

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes 

10、解锁

mysql> unlock tables;

本文出自 “卡卡西” 博客，请务必保留此出处http://whnba.blog.51cto.com/1215711/1605720