Mysql主从备份及恢复总结

周翔

　　Mysql主从实现
　　1、配置主从节点的服务配置文件
　　1.1、配置master节点：
　　[mysqld]
　　binlog_format=mixed
　　log-bin=master-bin
　　server-id=1
　　port=3306
　　datadir=/mydata/data
　　socket=/tmp/mysql.sock
　　1.2、配置slave节点：
　　[mysqld]
　　binlog-format=mixed
　　log-bin=mysql-bin.log
　　server-id=2
　　relay-log=/mydata/relaylogs/relay-bin(需要创建/mydata/relaylogs/后chown -R mysql.mysql /mydata/relaylogs/)
　　port=3306
　　datadir=/mydata/data
　　socket=/tmp/mysql.sock
　　2、创建复制用户
　　主：
　　mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.4.%  IDENTIFIED BY '12345';
　　mysql> flush privileges;
　　3、为备节点提供初始数据集
　　锁定主表，备份主节点上的数据，将其还原至从节点；在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。
　　注：通过全备可以看出mysql-bin的初始位置，
　　show master status 可以看出目前的空间位置。
　　通过全备和增量备份即可还原。
　　4、为了测试效果，先在master机器上创建测试库
　　mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> use test;
　　Database changed
　　mysql> create table if not exists test (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
　　Query OK, 0 rows affected (0.02 sec)
　　mysql> insert into test.test values(1,"liudehua"),(2,"zhangxueyou”);
　　Query OK, 2 rows affected (0.00 sec)
　　Records: 2  Duplicates: 0  Warnings: 0
　　mysql> select * from test.test;
　　+----+-----------+
　　| id | name      |
　　+----+-----------+
　　|  1 | liudehua |
　　|  2 | zhangxueyou |
　　+----+-----------+
　　2 rows in set (0.00 sec)
　　5、导出master数据库多于slave数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
　　导出数据库之前先锁定数据库
　　mysql> flush tables with read lock;
　　# mysqldump -uroot -p12345  test > /opt/test.sql
　　然后将导出的test.sql文件上传到slave机器上
　　scp test.sql root@192.168.4.204:/root/
　　6、启动从节点的复制线程
　　记录mysq-bin文件和位置,从端可以从下面这个点开始同步

　　7、在slave数据库中导入从master传过来的数据。
　　mysql> CREATE DATABASE  test  CHARACTER SET utf8  COLLATE utf8_general_ci;   #先创建一个test空库，否则下面导入数据时会报错说此库不存在。字符集需要相同与主库
　　mysql>  use test;
　　#mysql -uroot -p test < test.sql
　　8、准备同步
　　mysql> stop slave;
　　mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.203',
　　-> MASTER_USER='slave',
　　-> MASTER_PASSWORD='12345',
　　-> MASTER_LOG_FILE='master-bin.000004',
　　-> MASTER_LOG_POS=2327;
　　mysql> start slave;
　　mysql> show slave status \G;

　　9、最后解锁主库并写入数据进行同步验证:
　　master:
　　mysql> unlock tables;
　　mysql> insert into test.customers values (5,&quot;liming&quot;,55),(6,&quot;guofucheng&quot;,55);
　　Query OK, 1 row affected (0.00 sec)
　　slave: