MySQL主从备份

来源：互联网发布：去学校培训seo多少钱编辑：程序博客网时间：2024/06/02 03:48

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中创建同步账户

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

如：允许所有ip使用该用户名和密码登陆

mysql> grant replication slave on *.* to 'db_backup'@'%' identified by 'root';

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 >

0 0