MySQL Master and slave settings

MySQL Master-Slave introduction

MySQL master-slave is also called AB responsible, that is, A and b two machines from the back, in a some of the data, the other B can follow some data, both data synchronization;
MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave;
Application Scenario: Backup use, only use the main machine, from the machine backup, when the main machine down, switch from the machine normal access, shunt use, but from the machine can not write data;

The steps are: a set profile-a restart service-A to create a master-slave account-lock A's database write function-backup a all database-B set profile-B Restart service-The A backup file is uploaded to the B machine-B to create a database like a and restore the data as a touch-turn off the sync function of B-set synchronization parameter Unlock write function;

Primary MySQL configuration settings configuration file

vim /etc/my.cnf增加server-id=2                    //增加server-id为2log_bin=test01                //设置log_bin名为test01

/etc/init.d/mysqld restart            //重启mysql服务

Create a test001 database

In fact, this step can be omitted, meaning is to create a new test database test001, copy the contents of MySQL to test001

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql      //备份mysql数据库mysql -uroot -p123456 -e "create database test001"        //创建test001数据库mysql -uroot -p123456 test001 < /tmp/mysql.sql            //将刚刚备份的mysql数据库的内容恢复到新建的test001上

Create a master-slave MySQL Account

mysql -uroot -p123456                //登录mysql，在mysql设置增加用户权限grant replication slave on *.* to ‘repl‘@‘192.168.188.3‘ identified by ‘123456‘;        //创建用户repl限定为从ip登录，限定权限

Lock Database Write function

Use in login MySQL

flush tables with read lock;            //锁定数据库服务暂时无法写；show master status;                    //查看主服务的位置与id

To back up all databases

mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql    //将zrlog数据库备份mysqldump -uroot -p123456 mysql > /tmp/mysql.sql //将mysql数据库备份mysqldump -uroot -p123456 test001 > /tmp/test001.sql //将test001数据库备份mysqldump -uroot -p123456 db1 > /tmp/db1.sql //将db1数据库备份

Configure settings from MySQL configuration file

vim /etc/my.cnf        增加        server-id=3                //只增加一行server-id

/etc/init.d/mysqld restart        //重启mysql服务

scp 192.168.188.2:/tmp/*.sql /tmp/        //将主服务器上的备份传递到从机器的相应目录

mysql -uroot -p‘123456‘            //登录mysql

Create a database corresponding to the primary server

create database test001;            //这里必须对应主服务器的数据库，A上有的B也必须创建；create database db1;create database zrlog;

Restore backup data to the appropriate database

mysql -uroot -p‘123456‘ db1 < /tmp/db1.sqlmysql -uroot -p‘123456‘ test001 < /tmp/test001.sqlmysql -uroot -p‘123456‘ zrlog < /tmp/zrlog.sql

To turn off synchronization from a service

stop slave;

Setting synchronization parameters

change master to master_host=‘192.168.188.2‘, master_user=‘repl‘, master_password=‘123456‘, master_log_file=‘test01.000001‘, master_log_pos=664383;

Note: The Master_log_file and Master_log_pos parameters here are the show master status of master server A, respectively, and the command

Mysql> Show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
| test01.000001 | 664383 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

Turn on synchronization from service

start slave;

Unlocking the Write function of the master service

Log on to master server A for MySQL

unlock tables;        //解锁主服务器的写功能

Detection

show slave status\G            //只要看Slave_IO_Running与Slave_SQL_Running是否正常drop database test001;        //主上删除test001数据库；show databases;                //从上查看数据库列表，发现test001数据库消失；

Note: If the operation is deleted from the server, then the master-slave configuration will be automatically disconnected, after the two-party data is consistent, turn off the synchronization function, re-specify the synchronization parameters, and then open the synchronization line;

Other configurations

Vim/etc/my.cnf

Primary server (after the master configuration, from no configuration)

binlog-do-db=            //仅同步指定数据库binlog-ignore-db=        //忽略指定的数据库

From the server (after configuration, the master does not have to be configured)

replicate_do_db=                //仅同步指定数据库replicate_ignore_db=        //忽略指定的数据库replicate_wild_do_table=        //如test.%，支持通配符%，同步指定更新某表replicate_wild_ignore_table=    //忽略更新某表

MySQL Master and slave settings