Master-slave replication for mysql and master-slave replication for mysql

Master-slave replication for mysql and master-slave replication for mysql

Mysql master-master replication Summary

I. Main Ideas of Mysql Master/Slave replication:

1. mysql replication essence:

It is another MySQL database server that executes the binary log of this data change on the local machine. Therefore, it is very importantBinary logs must be enabled in the mysql database.;

2. Mysql master-slave replication:

That is, A is the primary database, B is the slave database, and B re-executes the binary log of Data Change in A in its database ;(B will only change with A, and A will not change with B)

3. Mysql master replication:

That is, database A and database B synchronize data to each other. Both database A and database B re-execute the binary logs of each other's data changes in their own databases,Is the integration of two master-slave Replication;(A and B follow each other to make changes)

2. Mysql master-master replication procedure:

Assume that database A and database B must implement master-master replication:

1. Create an account and password for logon from the slave database in the master database, and authorize the account:

Create an account:

Create user 'myuser' @ 'localhost' identified by 'mypassword ';

Authorization:

 

2. Modify the mysql configuration file. Generally, MySQL configuration files in Linux are stored in/etc/my. cnf (the configuration file in windows is mysql. ini)

Add the following configuration information to the configuration file of database A: (for information, see the mysql_copy_update_info.txt file)

# Note that the location of the information must be filled in the mysql configuration file, that is, [mysqld] under the [mysqld] Tag # Any natural number n, as long as the two MySQL hosts are not repeated, the start value of server-id = 1 # can be used. Generally, enter the nth primary MySQL instance. This is the first master MySQLauto_increment_offset = 1 # Step value auto_imcrement. Generally, for n primary MySQL instances, enter nauto_increment_increment = 2 # enable binary log-bin = mysql-binlog-error =/mysqldata/mysqld. logpid-file =/mysqldata/mysqld. pid # IP address of the master database corresponding to this slave database master-host = 192.168.15.185 # in the corresponding master database, the account master-user = backup that can be logged on to this database # in the corresponding master database, the password that can be logged on to this database: master-password = 123456 # the port number for logging on to the corresponding master database: master-port = 3306 # the database to be synchronized. By default, all databases are used, replicate-do-db = phpcmsv9 # databases that are not synchronized, replicate-ignore-db = mysqlmaster-connect-retry = 60

The configuration information added in the configuration file of database B is exactly the same as that added in database A, except that the server-id and auto_increment_offset are different;

3. Set the encoding format (otherwise, garbled characters may occur in Chinese ):

Hosts file)

[mysql]default-character-set=utf8[mysqld]default-character-set = utf8character_set_server = utf8[mysqld_safe]default-character-set = utf8[mysql.server]default-character-set = utf8[client]default-character-set = utf8

4. Restart the mysql database:

Mysql command: service mysqld restart;

5. view the binary file name and its location of the corresponding master database:

Use the mysql command in the master database: show master status;

 

6. Inform the database of the binary file name and its location of the corresponding master database:

When database A is the master database and database B is the slave database, run the mysql command in database B: (for details, see the change_master_to_info.txt file)

change master to master_host='172.20.109.14',master_user='mysql913',master_password='123456',master_log_file='mysql-bin.000056',master_log_pos=151744220;

B is the master database and A is the slave database. Execute the same command as above in database A and modify the parameters as appropriate;

7. start their respective slave:

Run the mysql command: slave start in each database;

8. view the status after slave is started:

Run the mysql command: show slave status \ G in each database;

Mainly observe:

(1) whether the two parameters in the red circle are both YES. If not, an exception occurs. Check the error prompt in the first line to handle the exception, for common exception handling, see section 4;

(2) the red line is the database to be copied. Check whether the database to be copied is correct. If it is incorrect, you need to modify the information in the mysql configuration file for a long time and execute it again from the beginning;

 

9. Complete master-master replication configuration and perform data testing:

Iii. Mysql master-slave replication procedure:

Similar to the master-slave replication steps above, the master-slave replication only needs to change master to for both databases, you only need to enable the binary and server-id configurations for the master database. The configuration information for the slave database is the same as that for the master database;

4. How to add a database to an existing Master:

1. Create a database based on your needs on mysql and create a required table to add data;

2. Create the corresponding database on another mysql instance and copy the data in the database;

3. Modify replicate-do-db in the mysql configuration file to add a new row. The value is the database to be added, and then restart the mysql service;

4. Run the linux Command to go to mysql and check whether the slave is correctly started. If an exception occurs during startup, ensure that the Server Load balancer instance is started properly;

5. The newly added database is in the master-master replication chain for testing;

5. How to remove a database from an existing Master:

1. modify the configuration file corresponding to mysql separately, comment out or delete the replicate-do-db of the database to be removed;

2. Restart the mysql service;

3. Go to mysql and view the log file information of mysql: change master status;

4. Execute the command line start slave until MASTER_LOG_FILE = "mysql-bin.000075", MASTER_LOG_POS = 216, and switch the location of binary log information read from the database to the existing location of the corresponding master database;

5. Remove a database;

Vi. troubleshooting of common Mysql Master/Slave replication exceptions:

1. Slave_IO_State: Waiting to reconnect after a failed regiled on master exception:

Solution:

Execute authorization commands for the corresponding account on the corresponding master database

Grant replication slave on *. * to "repl '@' % 'identified by 'sangfordb ';

Flush privileges;

Then stop slave and start slave again.

2. Slave_IO_State: connecting to master

Solution:

The reason is that this account cannot connect to the master database. Can I view the remote login of this account? Is the Linux Firewall port open?

3. slave_ SQL _running: NO exception:

Solution:

Skip the SQL statement that reports an error. Execute the mysql command in the slave database:

Set global SQL _SLAVE_SKIP_COUNTER = 1)

4. If the Server Load balancer starts normally, data is not synchronized:

Cause: the reason for this is that the file name and location of the binary information obtained from the master database are inconsistent with the latest data location in the master database;

Solution: view the binary file name and information of the master database: change master status; then execute: start slave until MASTER_LOG_FILE = "mysql-bin.000075", MASTER_LOG_POS = 216; command in the slave database, update the binary file information of the master database obtained from the database to the existing status of the master database. If the master database is copied, execute the command in the two mysql databases;

VII. Post-processing ideas for abnormal shutdown of Mysql master and Master/Slave replication servers:

(1) How does the administrator know if an exception occurs in mysql or slave:

1. slave exception: Write a shell script and use nagios to monitor two yes (Slave_IO and Slave_ SQL processes) of slave. If only one or zero yes is found, it indicates that the master or slave node has a problem and sends a text message alarm;

(2) steps for the Administrator to manually handle exceptions:

Simply restart the abnormal server or mysql. The changed data in the other database is automatically synchronized to the restarted mysql database;

8. A database or table cannot be synchronized due to data reasons in Mysql master-master replication:

1. Export the. SQL file of the database or table that is not synchronized, and run the following command:

Mysqldump-uroot-p -- master-data -- single-transaction-R -- databases zzcp03> zzcp03. SQL

2. Find the current log file and location (change master to…) in the exported SQL statement ...)

3. stop the slave in the other database, and then start salve from the location in the SQL file. The command is executed:

Start slave until MASTER_LOG_FILE = "mysql-bin.000075", MASTER_LOG_POS = 769;

4. Then let salve start;

5. Check whether the Server Load balancer instances in the two mysql instances are started normally. If the Server Load balancer instances are not started properly, ensure that the table data can be synchronized;

Two function codes are provided:

Export the addition, deletion, and repair data of a database or table when the slave does not synchronize information, and it carries binary file information:

mysqldump -uroot -p --master-data --single-transaction -R --databases zzcp03 > zzcp03.sql 

Let the master binary information obtained by slave start from the specified position:

start slave until MASTER_LOG_FILE="mysql-bin.000075", MASTER_LOG_POS=769;