MySQL Master master replication Summary collation

MySQL Master master replication Summary collation

First, Mysql main master, master and slave replication main idea:

1, MySQL replication essence:

just the rest. The MySQL database server executes the binary log of this data change again on this computer, so it is important that the binary log must be turned on in the MySQL database ;

2, Mysql master-slave replication:

is that A primary database,b is from the database,b will Change the data in a binary log in its database re-executed again; (b will only be with A Change, A not with B Change ）

3. Mysql master replication:

is that A and b databases synchronize data with eachother, anda and b will re-execute the binary log of each other's data in its own database, is the integration of two master-slave replication ; (a and B Follow each other to change )

Second, the main Mysql master copy operation steps:

assume that A Library,B Library to achieve primary master replication:

1, in the main library to create a login from the library account, password, and to authorize the account:

Create an account:

CREATE USER ' myuser ' @ ' localhost ' identified by ' mypassword ';

Authorized:

2. Modify mysql configuration file: mysql configuration files in general Linux are all in /etc/my.cnf ( The configuration file in Windows is mysql.ini)

the A library configuration file adds the following configuration information: (see mysql_copy_update_info.txt file for information)

#注意在mysql配置文件中填写一下信息的位置, you must #任意自然数n for this location, the [mysqld] label [MYSQLD], as long as the two MySQL hosts do not repeat the server-id=1# start value. Generally fill the nth master MySQL. This is the first primary mysqlauto_increment_offset=1# stepping value auto_imcrement. Normally there are N main MySQL to fill in nauto_increment_increment=2# open binary log log-bin=mysql-binlog-error=/mysqldata/mysqld.logpid-file=/ mysqldata/mysqld.pid# the IP address of the main library corresponding to this library master-host=192.168.15.185# in the corresponding main library, the account that the library can log into is master-user=backup# in the corresponding main library. The password that this library can log on master-password=123456# the port number that is logged in the corresponding main library master-port=3306# the database that you want to synchronize, default all libraries, specify multiple libraries to add new rows to replicate-do-db= phpcmsv9# do not synchronize the database, specify multiple libraries to add new rows can be replicate-ignore-db=mysqlmaster-connect-retry=60

the additional configuration information for the B library configuration file is identical to the information added in the A library, except server-id and auto_increment_offset different outside;

3, set the encoding format (otherwise Chinese is prone to garbled):

The added configuration information is circled as follows, if some tags [] in the mysql configuration file is not the default, you manually add (see character_encoding_info.txt file for details)

[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 of the corresponding main library and its location:

use the MySQL command in the main library :Show master status;

6, in this library to inform the relative to the main library binary file name and its location information:

A Master library,b executes the mysql command in the b Library when it is from the library : (For more information see the file 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 Main Library,a is from the library, in a library to execute the same command, the parameters of the appropriate modification;

7, respectively, to start their respective slave:

Execute mysql command in the respective library separately : slave start;

8. Check the status of slave after startup:

Execute MySQL commands in their respective libraries : SHOW SLAVE status\g;

The main observation is:

(1) red Circle of the two parameters are Yes, if not, then there is an exception, look at the first line of the error prompt, exception handling, for the common exception handling see four to solve;

(2) Red line for the copy of the database, to see if it is necessary to replicate the database, if not correct, so long need to modify the MySQL configuration file information, re-executed again from the beginning;

9, complete the primary master replication configuration, data testing:

Third, Mysql master-Slave copy operation steps:

similar to the main master copy step above, except that primary master replication operates on two libraries, master-slave replication requires only the change master to operation from the library , only the primary library needsto be configured to turn on the binary,Server-id can, the configuration information from the library as above;

Iv. How to add a library to an existing master:

1, on the use of MySQL first create the library as needed, and create the necessary tables to add data;

2. Create the corresponding library on another MySQL , and copy the data from the corresponding library .

3, modify the MySQL configuration file replicate-do-db New Add a row, the value is to the newly added library, and then restart the mysql services;

4, then through the Linux command to enter MySQL, to see if the slave is properly started, if the boot has an exception to handle, to ensure normal startup;

5, the newly added library to the main master replication chain, test can be;

V. How to remove a library from the existing master:

1, modify the MySQL corresponding configuration file separately, will need to remove the library's replicate-do-db comment out or delete;

2, restart the MySQL service;

3. in MySQL , check The log file information ofMySQL separately: changemaster status;

4. re-command line start slave until master_log_file= "mysql-bin.000075", master_log_pos=216, Switch the binary log information from the library to the existing location of the corresponding main library;

5, complete the removal of a library function;

Vi. Common exception handling for Mysql Master and master- slave replication:

1. Slave_io_state:waiting to reconnect after a failed registration on Master exception:

Workaround:

in the corresponding Main Library on From new to corresponding account Execution Authorization Commands

Grant Replication Slave on * * to "repl ' @ '% ' identified by ' Sangfordb ';

FLUSH privileges;

then restart stop slave and start slave to

2, slave_io_state:connecting to master

Workaround:

The reason is that this account can not connect to the main library, check account login? Linux firewall port open?

3, slave_sql_running:NO Exception:

Workaround:

Just skip the SQL to execute the error and execute the mysql command from the library:

SET GLOBAL sql_slave_skip_counter = 1(after the equals sign there are a few errors, do not know to try, until show SLAVE STATUS to see the result of the exception resolved)

4, Slave start Normal, is not synchronizing data:

Cause: This occurs because the file name and location of the binary information that gets the main library from the library is inconsistent with the latest data location in the main library;

Workaround: View the binary file name and information for the main library:change master status; then execute from the library: start slave until master_log_file= "mysql-bin.000075", master_log_pos=216; command, the main library binary file information is updated from the library to the existing state of the main library, and if it is the primary master replication, it is in two MySQL can be executed separately;

Seven, Mysql Master and master- slave replication server abnormal stop after processing ideas:

(1) MySQL or slave The exception administrator how to know:

1,slave Abnormal learned: Write a shell script, with Nagios monitoring slave of two Yes ( Slave_io and Slave_sql process), such as the discovery of only one or 0 Yes , it indicates that the master or subordinate problem, send SMS alert;

(2) Steps for administrators to handle exceptions manually:

Just restart the server with the exception or MySQL , and the changed data in the other database will be automatically synced to the restarted MySQL library;

Viii. a library or a table in Mysql master replication cannot synchronize exceptions because of data reasons :

1. Export the library or data that is not synchronized with the table. SQL file, by command:

Mysqldump-uroot-p--master-data--single-transaction-r--databases zzcp03 > Zzcp03.sql

2, in the exported SQL to find the current log file and location (changemaster to ... )

3, the other corresponding database slave stop , and then let Salve from The SQL file location in the beginning, through the command implementation:

Start slave until master_log_file= "mysql-bin.000075", master_log_pos=769;

4, then let salve start up;

5, Check the two mysql slave whether the normal start, not abnormal processing, to ensure normal startup, so that the table can complete the data synchronization;

Two function codes are included:

Export a library or a table in the slave during the different steps of the information, delete, repair data, and with the binary information:

Let slave get the master binary information starting at the specified location:

Start slave until master_log_file= "mysql-bin.000075", master_log_pos=769;

MySQL Master master replication Summary collation