MySQL主从备份

2024-06-28 16:02:40

字体：大中小

来源：转载

供稿：网友

master：主服务器 slave：从服务器

1、开启master的binarylog

打开MySQL的配置文件

# vim /etc/my.cnf

添加以下代码

log-bin=mysql-binbinlog_format=mixedserver-id=1read-only=0binlog-do-db=<dbname>

重启master数据库

# /etc/inint.d/mysqld restart

2、导出master数据库

锁定master数据库

mysql> FLUSH TABLES WITH READ LOCK;

导出master数据库

# mysqldump --master-data -uroot -p <dbname> > <path>+<dbname>.sql

记录master的日志信息

mysql> show master status/G*************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: <dbname> Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

解除锁定

mysql> UNLOCK TABLES；

3、开启slave的binarylog

打开MySQL的配置文件

# vim /etc/my.cnf

添加以下代码

log-bin=mysql-binbinlog_format=mixedserver-id=2replicate-do-db=<dbname>relay_log=mysql-relay-binlog-slave-updates=OFF

重启slave数据库

# /etc/inint.d/mysqld restart

4、导入master的数据到slave中

在slave中创建相应的数据库

CREATE DATABASE `<dbname>` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

导入数据库

mysql> source <path>+<dbname>.sql

5、在master中创建同步账户

grant replication slave on *.* to '<master_user>'@'<slave_host>' identified by '<master_passWord>';

6、在slave中开启同步

mysql> CHANGE MASTER TO MASTER_HOST='<master_host>', MASTER_USER='<master_user>', MASTER_PASSWORD='<master_password>', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

此处的MASTER_LOG_FILE、MASTER_LOG_POS的值参考2中的master日志信息

mysql> show master status/G ***************** 1. row ***************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: < dbname > Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

重启slave数据库

# /etc/inint.d/mysqld restart

查看slave的slave线程是否开启

mysql> show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: <master_host> Master_User: <master_user> Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 639 Relay_Log_File: mysqld-relay-bin.001075 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: <dbname> 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: 233 Relay_Log_Space: 15668 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: 1 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Slave_IO_State: Waiting for master to send event 表示线程已开启，等待master的数据

7、遇到的问题

1、在my.cnf中加入log-bin=mysql-bin后仍无法开启binarylog

添加后需重启MySQL存在多个my.cnf，用find / -name “my.cnf” 找到其他的my.cnf，一般来说，其中有socket=…的一般都是有效的配置文件在my.cnf中添加的代码位置不对，尽量上移，在[mysqld]的最后面即可

2、slave的状态中有Slave_IO_Running: Connecting 错误

检查< master_host > 、< master_user >、< master_password >是否手误填写错误master服务器的3306端口没有允许外部访问，在master中添加防火墙例外。然后用telnet <master_host> 3306测试是否连通检查master中创建的同步账户的< slave_host >

上一篇：UDP协议

下一篇：阿里云ECS服务器在Mac下远程配置ftp服务器