MySQL dual-Machine hot backup Configuration implementation (problem summary)

In order to realize the redundancy of MySQL database, backup, recovery, load balancing and other functions, the two days have been learning and studying MySQL dual-machine hot standby, in fact, MySQL's dual-machine hot standby is the use of MySQL synchronization function of the two modes of "Master-Master" synchronization method to achieve. In the beginning of the search for data to configure the issue, so did not pay attention to the version of the problem, so many times did not succeed, later learned that the search method is not correct, the results of the search is the old version of the data, and finally by searching for the corresponding version number and finally configured according to the data successfully, Now the different versions of the two-machine hot-standby configuration method to organize and share it to facilitate the other to configure MySQL dual-machine hot spare 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 version of the primary server database, but not lower than the primary server version of the database, if you want to implement a dual-machine hot standby must be two database version, if you do not know the version of the two database can View

1. View before landing

$MYSQLHOME/bin/mysql–v//$MYSQLHOME refer to MySQL's installation directory

2. View after Login

A, Mysql>status

B, mysql>select version ();

Now the latest version of MySQL is 5.5.27, starting from version 5.1.7 the method of hot standby of two machines has changed, in the configuration file before version 5.1.7, some of the parameters can be used in the version 5.1.7 after the release. Now the metaphor is described in terms of the configuration of the two-machine hot-standby versions prior to version 5.1.7 and version 5.1.7.

Make some definitions of the experiment environment before describing the configuration process, which is convenient for the following description

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

First, version 5.1.7 ago
1. Set Mysql_a
To edit my.cnf, add the following configuration under [Mysqld]: (the configuration that already exists in the MY.CNF does not have to be configured repeatedly)
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 make a log to another directory if you do not set a log name for the default host name
IP address or domain name of the master-host=192.168.100.200//master server
master-port=3306//port number of the primary database
MASTER-USER=ASYMT//Synchronizing the database user
master-password=123456//Synchronizing the password of the database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time lag for reconnection
BINLOG-DO-DB=ASYMT//Logging Database
Binlog-ignore-db=mysql//Do not log the database, this avoids the permissions set on master and so on is 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, the number of databases adds a few rows.

Then set the user account to synchronize the 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 to use the following statement to implement this function
Mysql> GRANT FILE on *. * to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';
Global FILE and select permissions must be granted if you want to have permissions on the slave to execute the "load TABLE from master" or "Load DATA from master" statements:
Mysql>grant file,select,replication SLAVE on * * to ' asymt ' @ ' 192.168.100.200 ' identified by ' 123456 ';
Lock an existing database, back up the current data, and restore the data on Mysql_b
Lock Database
Mysql> FLUSH TABLES with READ LOCK;
Do not exit this terminal, or the lock will not take effect; there are two ways to back up a database: Go directly to the MySQL data directory and then package 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, it is recommended to use the first method to back up the database, then extract the backup data on the MYSQL_A to the database data directory and set the permissions and the owner, and then use the" Unlock tables "statement to release the lock, and finally restart the database. Mysql_b

2. Set Mysql_b
As with Mysql_a, edit the MY.CNF and add the following configuration under [Mysqld]: (my.cnf the existing configuration does not have to be configured repeatedly)
server-id=2//service ID, each database should be different
Log-bin=log_name//log file name, where you can make a log to another directory if you do not set a log name for the default host name
IP address or domain name of the master-host=192.168.100.100//master server
master-port=3306//port number of the primary database
MASTER-USER=ASYMT//Synchronizing the database user
master-password=123456//Synchronizing the password of the database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time lag for reconnection
BINLOG-DO-DB=ASYMT//Logging Database
Binlog-ignore-db=mysql//Do not log the database, this avoids the permissions set on master and so on is 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, the number of databases adds a few rows.

Then set the user account to synchronize the 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 to use the following statement to implement this function
Mysql> GRANT FILE on *. * to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';
Global FILE and select permissions must be granted if you want to have permissions on the slave to execute the "load TABLE from master" or "Load DATA from master" statements:
Mysql>grant file,select,replication SLAVE on * * to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';

Finally restart the database

3. Log in to the database on Mysql_a and mysql_b respectively 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 Slave I/O and SQL threads are working properly

To this, the MySQL version 5.1.7 the previous dual-machine hot spare has been configured successfully

Second, after version 5.1.7 (in version 5.5.17 for example)
As in 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, on the mysql_a is set to 1, on the Mysql_b is set to 2.
Log-bin=log_name//log file name, where you can make a log to another directory if you do not set a log name for the default host name
BINLOG-DO-DB=ASYMT//Logging Database
Binlog-ignore-db=mysql//Do not log the database, this avoids the permissions set on master and so on is synchronized to the slave, if there is no limit to this, you can not set this parameter.

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

Mysql_a:

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

Global FILE and select permissions must be granted if you want to have permissions on the slave to execute the "load TABLE from master" or "Load DATA from master" statements:
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 ';

Global FILE and select permissions must be granted if you want to have permissions on the slave to execute the "load TABLE from master" or "Load DATA from master" statements:
Mysql>grant file,select,replication SLAVE on * * to ' asymt ' @ ' 192.168.100.100 ' identified by ' 123456 ';

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

Separate the Basic Law mysql_a and MySQL on Mysql_b view the primary 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 |
+---------------+----------+--------------+------------------+

Records the values of the file and Position items, which are to be used later.

Log in to MySQL on Mysql_a and Mysql_b separately and set various parameters for the main server:
Mysql_a:

Mysql> Change MASTER to
-master_host= ' 192.168.100.200 ',//IP for synchronous database
-master_user= ' asymt ',//users synchronizing the database
-master_password= ' 123456 ',//Sync database Password
Master_log_file= ' mysql-bin.003 ',//The file name of the primary server binary log (the parameters that are required to be remembered earlier)
master_log_pos=73; The starting position of the log file (the parameters that you need to remember earlier)

Mysql_b:

Mysql> Change MASTER to
-master_host= ' 192.168.100.100 ',//IP for synchronous database
-master_user= ' asymt ',//users synchronizing the database
-master_password= ' 123456 ',//Sync database Password
Master_log_file= ' mysql-bin.002 ',//The file name of the primary server binary log (the parameters that are required to be remembered earlier)
master_log_pos=70; The starting position of the log file (the parameters that you need to remember earlier)

Then run "slave start" in Mysql_a and Mysql_b MySQL to start the thread that synchronizes the database.

View the status of the master and slave servers

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

At this point, version 5.5.17 's dual-machine hot standby configuration is complete

Finally, in the database of Mysql_a and mysql_b to insert a large number of data to see the effect, if the database inserted data when prompted "XX is read only", please run "$MYSQLHOME/bin/mysqladmin-u <username >-P flush-tables "command to resolve

MySQL Dual machine hot standby implementation

1, the establishment of two-way MySQL database hot standby, first set up a copy account, account name and password for "Slaveuser", execute the following SQL statement:

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

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

2. Modify the MY.CNF configuration file of the main standby machine

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.%

If you need to synchronize cross-Library operations, use the –replicate-wild-do-table=db_name.% option

#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.%) functions similar to replicate-do-db, differs from REPLICATE-DO-DB and supports cross-library updates

Annotations:

Server-id = 1
Server ID number, integer value, ensuring that a single server is uniquely identified to
Log-bin=mysql-bin
Open Binary Log

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

Slave-skip-errors =all is a skip error, proceed with the copy operation (optional)

Log-slave-updates

Log-slave-updates This parameter must be added, otherwise it will not give updated records into 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 machine executes show slave status/g, if the replication status slave_io_running,slave_sql_running is yes, the replication is configured correctly.

Note: The above configuration for the primary standby database data consistent operation, to some operations do not log MySQL synchronization logs, you can use "set Sql_log_bin = 0 or 1;" Set the switch that logs the MySQL sync log on.

Deployment Issues Summary

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

mysql> slave stop;

mysql> Reset Slave;

The reason for this problem should be that MySQL had done the master and slave

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

Modify the/etc/profile file

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. Start slave after use show slave STATUSG, check error error reconnecting to master ' [email protected]:3306 '-retry-time:60 retries:86 400

Then log in to 192.168.0.100 primary database, allowing data to be synchronized from the database;

Grant Replication Slave on * * to [e-mail protected] identified by ' ****** '

Restart from the database again

4./ETC/MY.CNF invalid, MySQL does not read the file when it starts

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

5. Check the path of the RPM command installation software

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

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

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

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

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

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

c) Restart Slave

MySQL dual-Machine hot backup Configuration implementation (problem summary)