MySQL 5.5 Master-slave sync settings note sharing _mysql

First modify the MY.CNF configuration of Master (10.1.123.197)

Add the following in [Mysqld]:

Copy Code code as follows:

Log-bin=mysql-bin
Log-bin-index=mysql-bin.index
Server-id = 1
Sync_binlog=1
Binlog_format=mixed

Then specify the database to be synchronized and ignore the database that does not need to be synchronized

Copy Code code as follows:

Binlog-do-db = TestDB
binlog-ignore-db = MySQL
Binlog-ignore-db = Performance_schema
Binlog-ignore-db = Information_schema

In [mysqldump], modify the content to

Copy Code code as follows:

Max_allowed_packet = 32M

Note: Make sure Max_allowed_packet has a larger value, such as Max_allowed_packet = 100M

Restart MySQL:

Copy Code code as follows:

Service MySQL Restart

Create a replication user on Master

Copy Code code as follows:

CREATE USER funsion;
GRANT REPLICATION SLAVE on *.* to funsion identified by ' jb51.net ';
# test Environment password is Xyzzy

+ ---------------------------------------------------------- +

Then modify the configuration of Slave (10.1.123.160) and modify the MY.CNF

Add the following in [Mysqld]

Copy Code code as follows:

server-id=2
Log-bin = Mysql-bin
Relay-log-index = Slave-relay-bin.index
Relay-log = Slave-relay-bin

Sync_master_info = 1
Sync_relay_log = 1
Sync_relay_log_info = 1

# above three jargon is only used in MySQL 5.5

Enter Mysql-u root-p
Go to the MySQL command line and enter

Copy Code code as follows:

Change MASTER to Master_host = ' 10.1.123.233 ', master_user = ' funsion ', Master_port = 3306, Master_password = ' ifunsion.co M ';

Change MASTER to master_connect_retry=30;

In the input START SLAVE;

+-----------------------cannot start if an error occurs-------------------+

Access to Master's database
Enter Show Master Status\g
Record the Log-bin file currently in use, and position (for example, the log is now mysql-bin.000001, location is 107)

And then into the slave database.
Enter the stop slave;

Copy Code code as follows:

Change Master to master_log_file= ' mysql-bin.000001 ', master_log_pos=107;

Finally, enter the start slave;

Last executed on the server

Copy Code code as follows:

Yum-y Install Ntpdate
Ntpdate cn.pool.ntp.org
Clock-w

Synchronize the time of several servers

+----------------------------Some of the commands that might be used-------------------------+

To perform a lock table:

Copy Code code as follows:

Flush tables with read lock;

The goal of this step is to make our master-slave process, there will be no new data in the main library, or it will cause trouble to our synchronization settings

The main library performs the unlock:

Copy Code code as follows:

Unlock tables;

The Reset Master command deletes all binary log files and clears the binary log index files.
The Reset Slave command deletes all files used for slave replication and starts over again.

+-----------------------Other reference documents (not validated)--------------------------+

To make a mirror image from a library if the library business cannot be stopped:
Case:
Server number 1th: main
Server number 2nd: from
Now because of the load problem, the server number 3rd will be the same as the server number 1th.
But no. 1th library can not stop, number 2nd can not stop, master-slave synchronization process can not stop (demanding).
You can do this:
In the main:

Copy Code code as follows:

mysqldump-uroot-pxxxx-h127.0.0.1--databases db1 DB2 db3--master-data=1 > Bak.sql

Number 3rd server slave stop;
Then import bak.sql into number 3rd from the server

Copy Code code as follows:

Slave start;

Server 3rd will automatically update the node from the moment of export.
Because--master-data=1 this parameter will add the change statement at the bottom after exporting the SQL file. If--master-data=0, it does not carry.
Very convenient, but only for the library is not too big case, the case exported a total of 6G library.