Centos6.5 Mysql主从同步备份操作记录

因项目需求，需要做一个数据库备份。于是从网上查了很多资料。最终通过执行数据库脚本来完成两个数据库之间的连接。为了巩固知识，在这里记录下实际操作以及操作时遇到的问题。

数据库主从复制的优点：
1、 如果主服务器出现问题， 可以快速切换到从服务器提供的服务，保证高可用性
2、 可以在从服务器上执行查询操作， 降低主服务器的访问压力
3、 可以在从服务器上执行备份， 以避免备份期间影响主服务器的服务

注意事项：
1、server-id必须唯一，一般使用ip的后三位
2、修改完my.cnf配置需要重启数据库服务service mysqld restart

操作系统：centos6.5
Mysql版本：5.6.24
主数据库ip：192.168.130.131
从数据库ip：192.168.130.132

一、配置主服务器
1、在主服务器上，设置一个从数据库的账户，使用REPLICATION SLAVE（从复制）赋予权限

mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.130.132' IDENTIFIED BY '123456';

2、修改主数据库的配置文件my.cnf，开启bin-log，并设置server-id的值，修改之后必须重启mysql服务

[mysqld]server-id=131  #主机标示，整数log-bin=/var/log/mysql/mysql-bin.log  #确保此文件可写，开启bin-logread-only=0  #主机，读写都可以binlog-do-db=test   #需要备份的数据库，多个写多行binlog-ignore-db=mysql #不需要备份的数据库，多个写多行#可以通过mysql>show variables like 'log_%'; 验证二进制日志是否已经启动。

成功之后的展示如图所示：

3、查看主服务器master状态，查看当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复。

mysql> show master status;

成功之后的展示如图所示：

二、配置从服务器

1、修改从数据库的配置文件my.cnf

[mysqld]server-id=132 #设置从服务器id，必须与主服务器不同log-bin=/var/log/mysql/mysql-bin.log  #确保此文件可写，开启bin-logreplicate-do-db=test   #需要同步的数据库，多个写多行replicate-ignore-db=mysql #不需要同步的数据库，多个写多行

2、远程导入主数据库的数据

#将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2#加上-C参数可以启用压缩传递mysqldump --host=192.168.130.131 -ubackup -p123456 -C --databases test |mysql --host=192.168.130.132 -uroot -proot test 

3、配置主从同步

mysql>change master to    ->master_host='192.168.130.131',    ->master_user='beckup',    ->master_password='123456',    ->master_log_file='mysql-bin.000001',    ->master_log_pos=524;#log_file与log_pos是主服务器master状态下的File与Position

4、启动主从配置

mysql> start slave;#验证配置结果mysql>show slave status\G;#配置结果*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.130.131                  Master_User: backup                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 6982               Relay_Log_File: VM_bl_beifen_centos-relay-bin.000002                Relay_Log_Pos: 6965        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: test          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: 6982              Relay_Log_Space: 7152              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: 131                  Master_UUID: 6238decc-93a9-11e7-9b11-52540079877f             Master_Info_File: /mnt/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: 01 row in set (0.00 sec)ERROR:No query specified

配置结果重点关注提示
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都为Yes，则说明配置成功！

5、查看bin-log日志

mysql>show binlog events\G;#正常显示结果*************************** 12. row ***************************   Log_name: mysql-bin.000001        Pos: 985 Event_type: Query  Server_id: 1End_log_pos: 1075       Info: use `test`; delete from user where id=2*************************** 13. row ***************************   Log_name: mysql-bin.000001        Pos: 1075 Event_type: Query  Server_id: 1End_log_pos: 1175       Info: use `test`; alter table user add name varchar(50)

三、可能遇到的问题

1、重启数据库服务：
Starting MySQL. ERROR! The server quit without updating PID file (/mnt/data/localhost.localdomain.pid)

解决方案：
请检查log-bin的配置目录的用户权限是否为mysql用户，如果不是则更改该目录的权限，确保文件可写。
命令： chown -R mysql:mysql /var/log/mysql/

2、远程导入主数据库数据的时候

ERROR 1130 (HY000): Host ‘192.168.130.132’ is not allowed to connect to this MySQL server

解决方案：
1、检查数据库连接的账号密码有没有填错
2、检查主数据库3306端口是否开启
3、检查主数据库配置给192.168.130.132的用户权限

3、mysqldump: Got errno 32 on write

解决方案：
磁盘空间不足，请清理磁盘之后再做操作。