Mysql_multi mode configuration MySQL database master-slave

As stated above: http://www.cnblogs.com/super-d2/p/3851957.html

Install MySQL;

Mysql_multi mode configuration MySQL database master-slave

Grant permissions to synchronize data with users for synchronization

Grant Replication Slave on * * to ' slave3306 ' @ ' 127.0.0.1 ' identified by ' 3306 ';

Flush privileges;

View the master-slave status at this time, as follows

Show master status;

Configure multiple databases:

The contents are as follows:

[Mysqld_multi]

Mysqld =/usr/local/mysql/bin/mysqld_safe

Mysqladmin =/usr/local/mysql/bin/mysqladmin

#user = MySQL

#password = My_password

[Mysqld1]

Socket =/usr/local/var/mysql1/mysql1.sock

Port = 3306

Pid-file =/usr/local/var/mysql1/mysql1.pid

DataDir =/USR/LOCAL/VAR/MYSQL1

#language =/usr/local/mysql/share/mysql/english

user = MySQL

Server-id = 1

[Mysqld2]

Socket =/usr/local/var/mysql2/mysql2.sock

Port = 3307

Pid-file =/usr/local/var/mysql2/mysql2.pid

DataDir =/usr/local/var/mysql2

#language =/usr/local/mysql/share/mysql/english

user = MySQL

Server-id = 2

[MYSQLD3]

Socket =/usr/local/var/mysql3/mysql3.sock

Port = 3308

Pid-file =/usr/local/var/mysql3/mysql3.pid

DataDir =/usr/local/var/mysql3

#language =/usr/local/mysql/share/mysql/english

user = MySQL

Server-id = 3

[Mysqld4]

Socket =/usr/local/var/mysql4/mysql4.sock

Port = 3309

Pid-file =/usr/local/var/mysql4/mysql4.pid

DataDir =/usr/local/var/mysql4

#language =/usr/local/mysql/share/mysql/english

user = MySQL

Server-id = 4

(2) Starting a DB instance

Mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf Start #启动

Mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf Stop #关闭

Mysqld_multi--DEFAULTS-EXTRA-FILE=/ETC/MYSQLD_MULTI.CNF Report #查看状态

(3) Master-slave configuration:

Go to the main library

[Email protected] mysql]# mysql-uroot-p-h127.0.0.1-p3306

mysql> Reset Master;

Then to from the library:

Slave stop;

Reset slave;

Slave start;

Take a look at the main library:

The connection from the library already exists;

(4), verify whether the real synchronization:

Log in to Main library 3306:

OK, prove the data synchronization is successful!

If the primary key ID is repeated, you can try the following:

Set global server_id = 13;

（

Question: Now that server_id I'm dynamically set, just like this
Set global server_id = 13; Will this information be lost after restarting MySQL?

Answer: With this status verification, there will be no impact

）

Finally, note a few common commands:

Show Processlist

Set global server_id = 13;

Slave stop;

Reset slave;

Slave start;

Reset Master;

Show grants for [email protected] ' 127.0.0.1 ';

Show privileges; Select User,host from Mysql.user; Change Master to master_host= ' 127.0.0.1 ', master_port=3306,master_user= ' slave3306 ', master_password= ' 3306 ', master_  Log_file= ' mysql-bin.000004 ', master_log_pos=1641; Show master status show Slave status\g; summed up:
In fact, first in the main library to give a user slave permissions, and then to from the library to the master from the library to point to the main library, the slave user is actually taken to do synchronous data, and then do the process of the main library server_id and from the library server_id can not be the same;