Multiple master nodes in MySQL correspond to one slave.

Export data table structure

Mysqldump-D-uroot-P superadmin> D:/superadmin. SQL

Allow MySQL to access a machine

Grant all on *. * to root @ "192.168.1.1" identified by "19870806 ";

Flush privileges;

Backup and disaster tolerance are often encountered during normal development. Specific services must be processed according to the scenario.

During the backup process, I encountered that multiple masters correspond to one slave. Generally, multiple masters correspond to one server.

As you may know, the master and slave backups all correspond to one MySQL instance.

But I have never thought of having multiple slave instances on a slave machine. In this case, you need to know about mysqld_multi, mysqladmin, and mysqld_safe.

MySQL is started when multiple slave instances are used. socket and port are required to access the client, and then the corresponding client is entered. You can use mysqladmin and mysqld_safe to start and close the database.

MySQL provides a management solution for multiple instances, namely mysqld_multi.

[Mysqld_multi]
Mysqld =/usr/local/MySQL/bin/mysqld_safe
Mysqladmin =/usr/local/MySQL/bin/mysqladmin
User = root
Password = test

# The MySQL Server
[Mysqld1]
Port = 3306
Socket =/tmp/MySQL. Sock
Skip-external-locking
Key_buffer_size = 256 m
Max_allowed_packet = 1 m
Table_open_cache = 256
Sort_buffer_size = 1 m
Read_buffer_size = 1 m
Read_rnd_buffer_size = 4 m
Myisam_sort_buffer_size = 64 m
Thread_cache_size = 8
Query_cache_size = 16 m
# Try Number of CPU's * 2 for thread_concurrency
Thread_concurrency = 8
Wait_timeout = 100
Interactive_timeout = 100
Max_connections = 1000
Log-bin = mysql-bin
User = root
Servers-id = 92001
# Binary logging format-mixed recommended
Binlog_format = mixed
Datadir =/var/MySQL/Data
[Mysqld2]
Port = 3308
Socket =/tmp/mysql3308.sock
Skip-external-locking
Key_buffer_size = 256 m
Max_allowed_packet = 1 m
Table_open_cache = 256
Sort_buffer_size = 1 m
Read_buffer_size = 1 m
Read_rnd_buffer_size = 4 m
Myisam_sort_buffer_size = 64 m
Thread_cache_size = 8
Query_cache_size = 16 m
# Try Number of CPU's * 2 for thread_concurrency
Thread_concurrency = 8
Wait_timeout = 100
Interactive_timeout = 100
Max_connections = 1000
Log-bin = mysql-bin
Servers-id = 98302
User = root
# Binary logging format-mixed recommended
Binlog_format = mixed
Datadir =/var/mysql3308/Data
Master-host = 114.80.81.85
Master-user = root
Master_password = slave
Master-connect-retry = 10

Here, datadir, socket, and port must point to different instances during compilation.

The following figure shows how to start a database:

/Usr/local/MySQL/bin/mysqld_multi -- defaults-file =/etc/My. CNF stop 1
/Usr/local/MySQL/bin/mysqld_multi -- defaults-file =/etc/My. CNF start 1

How to access the database Client

Mysql-uroot -- socket =/tmp/mysql3308.sock -- Port = 3308-P

Slave MySQL backup

Master Configuration

BINLOG-do-DB = db1
BINLOG-do-DB = DB2
BINLOG-ignore-DB = MySQL
Replicate-do-DB = db1
Replicate-do-DB = DB2
Replicate-ignore-DB = MySQL

If the master node fails, go directly to the slave configuration.
Log-slave-Updates

Slave-Skip-errors = all
Sync_binlog = 1
Server_id = 2 is different from the server_id of slave.
Note that the serverid Of My. CNF cannot be the same

Then restart the database and pay the access permission to slave.
Grant all on *. * to root @ "192.168.1.1" identified by "19870896"

Flush privileges;

Slave Configuration

The slave end may copy only some tables. Some tables do not need to be backed up as follows:

Some tables may be processed horizontally or vertically. If the first few digits of the table are the same, you can use the wildcard % to match

Replicate-wild-ignore-table = db1.listinglog20 %
Replicate-wild-ignore-table = db1.listingplan20 %
Replicate-wild-ignore-table = db1.showcaselog20 %

Of course, there will also be no wildcard. The solution is as follows:

Replicate-ignore-table = db1.batch _ item_tmp
Replicate-ignore-table = db1.batchitem

Restart MySQL after configuration.

Change master to master_host = '192. 168.11.11 ', master_user = 'root', master_password = 'slave ',
Master_port = 3306,
Master_log_file = 'mysql-bin.000214 ',

Master_log_pos = 342800;

Then run start slave;

Show slave status \ g after startup;

Seeing the final seconds_behind_master null indicates that there is no communication,

Stop slave at this time.

Set global SQL _slave_skip_counter = 1;

Enable slave
Start slave
Note: Show slave status \ G. Check the slave status.
Show Master Status \ G; check the status of the master.

Specific examination

Http://blog.zhanxb.com/post/361/

Http://renxiangzyq.iteye.com/blog/684038

Http://www.cnblogs.com/birdshover/archive/2010/01/27/1657670.html