MySQL Master-slave replication (scripting Mates)

First of all, there are two MySQL servers, I am the same MySQL multi-instance to achieve the master-slave replication, the experiment with the script, you can refer to the construction of multi-instance environment

Multi-instance environment construction HTTP://ABY028.BLOG.51CTO.COM/5371905/1892250

Master: 3306 (Master)

From: 3307 (slave)

Ensure the normal operation of MySQL multi-instance

NETSTAT-TLUNP | grep 330

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/8C/DE/ Wkiol1h8cxfbxajyaaayls69kuo076.png "" 818 "height=" "/>

View Log-bin and Server-id

Egrep "Log-bin|server-id"/data/{3306,3307}/my.cnf

/data/3306/my.cnf:log-bin =/data/3306/mysql-bin # Master Open

/data/3306/my.cnf:server-id = 1

/DATA/3307/MY.CNF: #log-bin =/data/3307/mysql-bin # slave can be turned off

/data/3307/my.cnf:server-id = 3

If you change the MySQL configuration file, you need to restart the database

/data/3306/mysql stop

/data/3307/mysql stop

/data/3306/mysql start

/data/3307/mysql start

Log in to the main library 3306

Mysql-uroot-p123456-s/data/3306/mysql.sock

To view MySQL configuration parameters

Mysql> Show variables like "Log_bin";

Mysql> Show variables like "server_id";

Authorize an account in the main library, dedicated for syncing, and then refresh permissions

Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 ';

Flush privileges;

Check the action you just made

Select User,host from Mysql.user;

Show grants for [email protected] '% ';

Lock table. You cannot create a database after you lock the table

Flush table with read lock;

Backup package (requires a separate window to ensure that the previous window cannot exit)

mysqldump-uroot-p123456--events-s/data/3306/mysql.sock-a-B | gzip >/opt/bak_$ (Date +%f). sql.gz

Once the package is complete, review the status again to ensure that the data is not written in the same way as the previous query state

Show master status;

Ching

Unlock tables;

Import the gzip file you just backed up into the 3307 database (full backup)

cd/opt/

Gzip-d bak_2017-01-06.sql.gz

Mysql-uroot-p123456-s/data/3307/mysql.sock < Bak_2017-01-06.sql

Incremental backup, login Configure synchronization parameters from library

Change MASTER to

Master_host= ' 192.168.6.45 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=459;

Perform the start slave operation on the salve, IO connection

Start slave;

To view the current status, there are a few things that are critical:

show slave status\g;

Slave_io_state:waiting for Master to send event

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0 #从库落后主库的秒数

MySQL Master-slave replication (scripting Mates)