Mysql-jira Dual Machine Hot standby

Primary server:192.168.1.23

from server:192.168.1.243

One, master server master configuration 1. Create a sync account, empower

Establish a connection account on the primary server for the slave server, where root is used, and the account must grant replication slave permissions. Since MySQL version 3.2 can be used by the replication to the dual-machine hot standby function operation.

MySQL>Grantreplicationon*. *  to ' Root '@'192.168.1.243'by'123456'  ; MySQL>privileges;

Once the synchronization connection account has been created, we can see if the connection is successful by accessing the primary server (master) database with the Replicat account on the slave server (Slave).

Enter the following command on the slave server (Slave):

[Email protected] ~]# mysql-h192. 168.1.  -uroot-p123456

2. Modify the MySQL configuration file

Find the MySQL configuration all in the directory, generally after the installation of the MySQL service, the configuration file will be copied one by one copies out into the/ect directory, and the configuration file named: my.cnf. That is, the exact configuration file directory is/ETC/MY.CNF

The MySQL configuration file in the project is in the/opt/product/mysql directory.

[Mysqld]server-ID1log-bin=mysql-bin               binlog-do-db = jirabinlog-ignore-db = MySQL

3. Restart the MySQL service

/ETC/INIT.D/MYSLQ restart

4. View the primary server status

mysql> flush tables with read Lock;mysql> show Master Status \g; 1. Row ***************************             file:mysql-bin.  000002         1290     Binlog_do_db:jira binlog_ignore_db:mysqlexecuted_gtid_set: 1  in Set (0.00  sec) error:no query specified

Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).

Note: The lock table is used here, in order to generate the environment in the new data, so that from the server location synchronization location, the initial synchronization is completed, remember to unlock.

mysql> unlock tables;

Second, from the server slave configuration 1. Modify the configuration file

Because this is in the main-from the way to achieve the MySQL dual-machine hot standby, so in the slave server is not in the establishment of synchronization account, directly open the configuration file my.cnf to modify, the same reason is the same as the modification of the main server, just need to modify the parameters are different. As follows:

[Mysqld]server-ID2log-bin=mysql-binreplicate-do-db =  Jirareplicate-ignore-db = Mysql,information_schema,performance_schema

2. Restart the MySQL service

After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

3. Specify the synchronization location with the change Mster statement

This step is the most critical step, after entering the MySQL operator interface, enter the following command:

//It is important to stop the slave service thread first, and if you do not do this it will cause the following operations to be unsuccessful. MySQL>stop Slave; MySQL>Change Master toMaster_host='192.168.1.23', Master_user='Root', Master_password='123456', Master_log_file='mysql-bin.000002', Master_log_pos=1290;
Mysql>start slave;

Note:master_log_file, Master_log_pos is determined by the status value identified by the master server (master). That's what it just called attention. master_log_file corresponds to file, Master_log_pos corresponds to position. Mysql 5.x or later does not support the option to specify the primary server in the configuration file.

If you follow the above steps, you will see the following situation:

4. View the slave server (Slave) status

Mysql>show slave status\g;*************************** 1. Row***************************slave_io_state:waiting forMaster toSend event Master_host:192.168.1.14master_user:root Master_port:3306Connect_retry: -Master_log_file:mysql-Bin.000002Read_master_log_pos:1290Relay_log_file:ha-Db-Relay-Bin.000002Relay_log_pos:1453Relay_master_log_file:mysql-Bin.000002 slave_io_running:yes slave_sql_running:yes Replicate_do_db:jira Replicate_ignore_db:mysql,information_schema,performance_schema replicate_do_table:replicate_ignore_ Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_error:skip_counter:0Exec_master_log_pos:1290Relay_log_space:1626until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_ Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1master_uuid:08337b95-6e04-11e7-a595-000c2994f6ec master_info_file:/Opt/Product/Mysql/Data/master.info Sql_delay:0Sql_remaining_delay:NULLSlave_sql_running_state:slave hasRead  AllRelayLog; Waiting forThe slave I/O thread to UpdateIt master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Cr  L:master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_position: 01Rowinch Set(0.00sec) Error:no query specified

Viewing the following two key values are yes, which means that the setting is successful from the server.

Slave_io_running:yes

Slave_sql_running:yes

Third, test synchronization

Reference: http://blog.csdn.net/huaweitman/article/details/50853075

Mysql-jira Dual Machine Hot standby