MySQL dual-Machine hot backup Configuration implementation (summary of issues)

In order to realize the redundancy of MySQL database, backup, recovery, load balancing functions, such as the two days have been learning and research MySQL dual-machine hot standby, in fact, MySQL dual-machine hot standby is the use of MySQL synchronous function in two ways inside the "master-master" synchronous way to achieve. In the beginning of the search data to configure not to pay attention to the version of the problem, so many times did not succeed, then know the search method is not correct, the results of the search for the data are the old version, and finally through the search for the corresponding version of the data finally configured to succeed, Now the different version of the two-machine hot standby configuration methods to organize and share the convenience of other people want to configure MySQL dual hot standby friends can be a success.

To achieve a hot standby version of MySQL is higher than 3.2, there is a basic principle is that the database version from the database can be higher than the primary server database version, but not below the primary server version of the database, if you want to achieve dual-machine hot standby must be two database version, if you do not know the version of the two database can use the following command View

1. View before landing

$MYSQLHOME/bin/mysql–v//$MYSQLHOME refers to the MySQL installation directory

2, after landing view

A, Mysql>status

B, mysql>select version ();

MySQL is now the latest version of 5.5.27, starting from the version of 5.1.7 hot standby method has changed, in the version of the 5.1.7 configuration file inside some of the parameters can be used in the version 5.1.7 after the. It is now described in terms of two-machine hot standby configurations based on version 5.1.7 prior and version 5.1.7.

Define the experimental environment before describing the configuration process to facilitate the following description

The first MySQL database server name is: Mysql_a,ip address is: 192.168.100.100; the name of the second MySQL database server is: Mysql_b, IP address: 192.168.100.200, both servers are used centos5.6 operating system, two MySQL installation directory are:/mysql, the Data directory are:/mysql/data, the configuration file directory are:/etc/ MY.CNF, the database to be synchronized is ASYMT

A, version 5.1.7 before
1, set Mysql_a
Edit My.cnf, add the following configuration under [Mysqld]: (My.cnf already exists in the configuration does not have to be duplicated)
Server-id=1//Service ID, this should be the default is 1 do not have to change
Log-bin=log_name//log file name, where you can create a log to another directory if not set then a log name for the default host name
MASTER-HOST=192.168.100.200//Primary server IP address or domain name
master-port=3306//Main database port number
MASTER-USER=ASYMT//Sync database users
master-password=123456//Sync Password for database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time difference between reconnection
BINLOG-DO-DB=ASYMT//Log database
Binlog-ignore-db=mysql//Does not log the database, which avoids the master's permission settings, etc. are synchronized to the slave, if there is no limit to this, you can not set this parameter.
If more than one database is added below, just add a few rows to the number of databases

Then set the user account for the synchronization database
Mysql> GRANT REPLICATION SLAVE on *.* to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';
4.0.2 previous versions, because replication is not supported by using the following statement to implement this feature
Mysql> GRANT FILE on *.* to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';
If you want to have permission to execute the "load TABLE from master" or "Load DATA from master" statement on Slave, you must grant the global FILE and SELECT permissions:
Mysql>grant file,select,replication SLAVE on *.* to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';
Lock existing databases, back up current data, and recover data on mysql_b
Lock Database
Mysql> FLUSH TABLES with READ LOCK;
Do not exit this terminal, otherwise the lock will not be effective; there are two ways to back up a database one is to go directly to MySQL's data directory and then pack the folders you need to back up the database, and the second is to use mysqldump to back up the database but add "--master-data "This parameter suggests using the first method to back up the database, then unzip the backup data on the MYSQL_A to the database data directory and set permissions and owners, then use the" Unlock tables "statement to release the lock and then restart the database Mysql_b.

2, set Mysql_b
As with Mysql_a, edit my.cnf, add the following configuration under [Mysqld]: (the configuration already exists in MY.CNF does not have to be duplicated)
server-id=2//service ID, each database should be different
Log-bin=log_name//log file name, where you can create a log to another directory if not set then a log name for the default host name
MASTER-HOST=192.168.100.100//Primary server IP address or domain name
master-port=3306//Main database port number
MASTER-USER=ASYMT//Sync database users
master-password=123456//Sync Password for database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time difference between reconnection
BINLOG-DO-DB=ASYMT//Log database
Binlog-ignore-db=mysql//Does not log the database, which avoids the master's permission settings, etc. are synchronized to the slave, if there is no limit to this, you can not set this parameter.
If more than one database is added below, just add a few rows to the number of databases

Then set the user account for the synchronization database
Mysql> GRANT REPLICATION SLAVE on *.* to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';
4.0.2 previous versions, because replication is not supported by using the following statement to implement this feature
Mysql> GRANT FILE on *.* to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';
If you want to have permission to execute the "load TABLE from master" or "Load DATA from master" statement on Slave, you must grant the global FILE and SELECT permissions:
Mysql>grant file,select,replication SLAVE on *.* to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';

Finally reboot the database

3, respectively landing mysql_a and Mysql_b on the database, to view the synchronization status:
Mysql–h Localhost–u Root-p
Mysql>show SLAVE STATUSG
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.100.100
Master_user:asymt
master_port:3306
Connect_retry:60
master_log_file:localhost-bin.000002
read_master_log_pos:1556
relay_log_file:localhost-relay-bin.000004
Relay_log_pos:51
relay_master_log_file:localhost-bin.000002
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:cicro,cicro
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:1556
Relay_log_space:51
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
1 row in Set (0.00 sec)
As you can see, the values for the slave_io_running and slave_sql_running two columns are "Yes", which indicates that the Slave I/O and SQL threads are running correctly

To this, the MySQL version 5.1.7 the previous two-machine hot standby has been configured successfully

Second, after version 5.1.7 (in version 5.5.17 for example)
Before the same version 5.1.7, edit the my.cnf configuration file on Mysql_a and Mysql_b, and add the following parameters:

Server-id=n//service ID, each database is different, "n" represents the ID number, set to 1 on the mysql_a, set to 2 on the mysql_b.
Log-bin=log_name//log file name, where you can create a log to another directory if not set then a log name for the default host name
BINLOG-DO-DB=ASYMT//Log database
Binlog-ignore-db=mysql//Does not log the database, which avoids the master's permission settings, etc. are synchronized to the slave, if there is no limit to this, you can not set this parameter.

Set up user accounts for the synchronization database
Restart the MySQL database on mysql_a and mysql_b, then log on to MySQL and run the following command

Mysql_a:

Mysql> GRANT REPLICATION SLAVE on *.* to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';

If you want to have permission to execute the "load TABLE from master" or "Load DATA from master" statement on Slave, you must grant the global FILE and SELECT permissions:
Mysql>grant file,select,replication SLAVE on *.* to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';

Mysql_b:

Mysql> GRANT REPLICATION SLAVE on *.* to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';

If you want to have permission to execute the "load TABLE from master" or "Load DATA from master" statement on Slave, you must grant the global FILE and SELECT permissions:
Mysql>grant file,select,replication SLAVE on *.* to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';

Use the previous method to lock the database and back up the data on the mysql_a and then revert to the Mysql_b to finally release the lock on the database.

Separate fundamental method mysql_a and mysql_b on MySQL view master server status
Mysql_a:

Mysql> Show Master STATUSG;
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.002 | 70 | ASYMT | MySQL |
+---------------+----------+--------------+------------------+
Mysql_b:

Mysql> Show Master STATUSG;
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | ASYMT | MySQL |
+---------------+----------+--------------+------------------+

Record the values of the file and Position items for later use.

Log on to MySQL on Mysql_a and Mysql_b, and set various parameters for the master server:
Mysql_a:

Mysql> Change MASTER to
-> master_host= ' 192.168.100.200 ',//Synchronize the IP of the database
-> master_user= ' asymt ',//Synchronizing database users
-> master_password= ' 123456 ',//Sync database Password
-> master_log_file= ' mysql-bin.003 ',///The file name of the primary server binary log (parameters previously required to remember)
-> master_log_pos=73; The starting position of the log file (parameters that are required to remember earlier)

Mysql_b:

Mysql> Change MASTER to
-> master_host= ' 192.168.100.100 ',//Synchronize the IP of the database
-> master_user= ' asymt ',//Synchronizing database users
-> master_password= ' 123456 ',//Sync database Password
-> master_log_file= ' mysql-bin.002 ',///The file name of the primary server binary log (parameters previously required to remember)
-> master_log_pos=70; The starting position of the log file (parameters that are required to remember earlier)

Then run "slave start" in the MySQL of Mysql_a and mysql_b to start the thread that synchronizes the database

View the status of the master-slave server

Mysql> Show PROCESSLISTG//Can view the MySQL process to see if there is a listening process

At this point, the version 5.5.17 dual-Machine hot standby configuration is complete

Finally, in the Mysql_a and Mysql_b database inserted a large number of data viewing effect, if the database inserted data prompts "XX is read only", please run the "$MYSQLHOME/bin/mysqladmin-u <username >-P flush-tables "command resolution

MySQL dual-machine hot standby implementation

1, set up a MySQL database two-way hot standby, first set up a replication account, account name and password for the "Slaveuser", the execution of the SQL statements are as follows:

GRANT REPLICATION SLAVE on *.* to ' slaveuser ' @ '% ' identified by ' slaveuser ';

#GRANT REPLICATION SLAVE on *.* to ' slaveuser ' @ ' redunhost ' identified by ' slaveuser ';

2, modify the main standby machine my.cnf configuration file

Server-id = 1

Log-bin=mysql-bin
Relay-log=relay-bin

Log-slave-updates

#slave-skip-errors = ALL (optional)

Master-host = Redunhost
Master-user = Slaveuser
Master-password = Slavepass

master-port=8004

#跨库操作配置

replicate-wild-do-table=db_name.%

Use the –replicate-wild-do-table=db_name.% option if you need to synchronize cross-Library operations

#replicate-do-db=db_name Specifies the database to replicate and does not natively support cross library updates

Replicate-do-table=db_name.t_name (db_name.%) is similar to the REPLICATE-DO-DB function, which differs from replicate-do-db to support cross library updates

Annotations:

Server-id = 1
The server ID number, the integer value, to ensure that uniquely identifying a single server can
Log-bin=mysql-bin
Open Binary Log

Relay-log=relay-bin
Relay Log Name
Master-host = Redunhost
Primary server IP address or host name, such as: 172.20.16.204
Master-user= Slaveuser
The name of the replication user account created on the primary server, such as the above: Slaveuser
Master-password=slaveuser
Replication user account password created on the primary server, such as the above created: Slaveuser
Relay-log-index=relay-bin
Relay Index file name

Slave-skip-errors =all is skipping error, continue copying (optional)

Log-slave-updates

Log-slave-updates This parameter must be added, otherwise it will not give the updated records to the binary file

The main standby machine performs reset master;stop slave;reset slave;start slave;

Restart the primary standby MySQL service, the main standby performs show slave status/g, if the replication status slave_io_running,slave_sql_running is yes, the replication has been configured correctly.

Note: The above configuration is consistent with the primary standby database data, for some operations do not record the MySQL synchronization log, you can use "set Sql_log_bin = 0 or 1;" Sets the switch to turn on logging of the MySQL synchronization log.

Summary of deployment issues

1. mysql is primarily a slave, but this error occurs when the change master is executed: Error 1201 (HY000): Could not initialize master!

mysql> slave stop;

mysql> Reset Slave;

The reason for this problem should be that MySQL did the master and slave before

2. Start Cobar times wrong error:java_home environment variable is not set.

modifying/etc/profile files

Export java_home=/usr/java/jdk1.6.0_23

Export JRE_HOME=/USR/JAVA/JDK1.6.0_23/JRE

Export classpath=.: $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar

Export path= $JAVA _home/bin: $PATH

Ldconfig

Log off the current user

3. After Start slave use show slave statusg check error error reconnecting to master ' slave@192.168.0.100:3306 '-retry-time:60 retries: 86400

Then log on to the 192.168.0.100 master database, allowing data to be synchronized from the database;

Grant replication Slave on *.* to slave@192.168.0.200 identified by ' Hu Jintao '

Restart from the database again

4./ETC/MY.CNF invalid, MySQL does not read the file at startup

Mysqld_safe--defaults-file=/etc/my.cnf

5. Check the rpm command to install the software path

RPM-QL Kit #列出rpm软件包的相关目录

RPM-QC Kit #列出rpm软件包的设置文档

RPM-QD Kit #列出rpm软件包的说明数据文件的完整路径

6. Start Slave error 1200 (HY000): The server is not configured as slave

A) Use the command show VARIABLES like ' server_id ' check

b If server_id is not the same as the configuration ID in my.cnf, use set GLOBAL server_id = 2; Modify the same value as in the configuration

c) Restart Slave