Install environment OS: CentOS6.5 database version: MySQL5.6.27 host A: 192.168.1.1 (Master) host B: 192.168.1.2 (Slave) The database version emphasized here, the reason is that the installation method before and after MySQL 5.6 is different. I encountered this problem during configuration. Here
Installation environment operating system: CentOS 6.5 database version: MySQL 5.6. 27 host A: 192.168. 1.1 (Master) host B: 192.168. 1.2 (Slave) indicates the database version, because the installation method before and after MySQL 5.6 is different. I encountered this problem during configuration. Here
Installation environment
Operating System: CentOS 6.5 database version: MySQL 5.6.27 host A: 192.168.1.1 (Master) host B: 192.168.1.2 (Slave)
The database version mentioned here is because MySQL is installed differently before and after MySQL 5.6.
I also encountered this problem during configuration. I hope you will not be confused here.
Note: Here is an CentOS installation of MySQL articles, which leads to the process of testing, hope to help everyone: http://blog.csdn.net/xlgen157387/article/details/49964557
Basic Environment Configuration
First, make sure that the firewall is enabled for port 3306. For more information, see [http://blog.csdn.net/xlgen157387/article/details/49964557].service iptables stop
Command to directly disable the firewall.
Then, ping the two machines to ensure that the two machines are the same.
Master Configuration
In Linux, the location of the MySQL configuration file is/etc/my.cnf
In this file, the configuration of the specified Master is as follows:
log-bin=mysql-binserver-id=2binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlbinlog-do-db=ufind_db
Here the server-id is used to identify a unique database. Here it is set to 2. When setting the slave database, you need to set it to another value.
Binlog-ignore-db: indicates the ignore database during synchronization.
Binlog-do-db: Specifies the database to be synchronized
The complete configuration is as follows:
1. Restart mysql:service mysqld restart
2. Enter mysql:[root@VM_221_4_centos ~]# mysql -u root -p
Press enter and enter the mysql password.
3. Grant the Slave database permission account to allow the user to read logs in the master database, and grant the File Permission to 192.168.1.2, that is, the Server Load balancer machine, but only grant the Server Load balancer machine the File Permission, you must also grant the replication slave permission to it.
In the Master database command line, enter:
>GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password'; >GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';>FLUSH PRIVILEGES
Here, the root user is used for synchronization. You can set it by yourself.
4. Restart mysql and log on to mysql. The master database information is displayed.
mysql> show master status;
mysql> show master status;+------------------+----------+--------------+----------------------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+----------------------------------+-------------------+| mysql-bin.000004 | 28125 | ufind_db | information_schema,cluster,mysql | |+------------------+----------+--------------+----------------------------------+-------------------+1 row in set (0.00 sec)mysql>
Here, File and Position are used when configuring Salve. Binlog_Do_DB indicates the database to be synchronized, and Binlog_Ignore_DB indicates the Ignore database. These are all specified during configuration.
In addition, if this step is alwaysEmpty set(0.00 sec)
, It indicates that my. cnf is not configured correctly.
Slave Configuration
1. First, modify the configuration file of the slave Database:/etc/my. cnf:
log-bin=mysql-binserver-id=3binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlreplicate-do-db=ufind_dbreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60
2. We can see that MySQL and later versions are not specified:
Master-host = 192.168.1.1 # Master's host IPmaster-user = rootmaster-password = mysql password # Master's MySQL password
3. This is also the configuration process for many searches on the Internet. They also specify the database version, but it is not said that the new version configuration is not applicable.
4. If the slave database is configured in MySQL5.6 or later, the above content is set, that is, the master-host and master-user information is specified, an error is returned when MySQL is restarted. The error message is as follows:
[root@VM_128_194_centos bin]# service mysqld restartShutting down MySQL... SUCCESS! Starting MySQL... ERROR! The server quit without updating PID file (/data/mysqldb/VM_128_194_centos.pid).[root@VM_128_194_centos bin]#
In this case, view the database error message (Database directory,/data/mysqldb/VM_128_194_centos.err). You can see:
2016-05-06 13:12:04 13345 [Note] InnoDB: Waiting for purge to start2016-05-06 13:12:04 13345 [Note] InnoDB: 5.6.27 started; log sequence number 28502112016-05-06 13:12:04 13345 [ERROR] /data/home/server/mysql-5.6.27/bin/mysqld: unknown variable 'master-host=192.168.1.1'2016-05-06 13:12:04 13345 [ERROR] Aborting
We can see thatmaster-host
The number of detected variables is an unknown variable, so an error occurs.
5. configuration in 5.6 and later versions is as follows:
After modifying the/etc/my. cnf file, restart MySQL (service mysqld restart)
Log on to the Slave mysql console and run the following command:
Mysql> stop slave; # disable Slavemysql> change master to master_host = '2017. 168.1.1 ', master_user = 'root', master_password = '000000', master_log_file = 'mysql-bin.000004', master_log_pos = 123456; mysql> start slave; # enable Slave
Specify the Master information here. master_log_file is the File option when configuring the Master. master_log_pos is the Position option for configuring the Master.
Then you can usemysql> show slave status;
View the configuration information:
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.167.1.1 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 28125 Relay_Log_File: VM_128_194_centos-relay-bin.000004 Relay_Log_Pos: 26111 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ufind_db Replicate_Ignore_DB: mysql 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: 28125 Relay_Log_Space: 26296 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: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd Master_Info_File: /data/mysqldb/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)ERROR: No query specifiedmysql>
You can see that the configuration is successful.
Add Slave database Slave to be synchronized
For various reasons, the test database is used during the test. Here I modify the my. in the cnf configuration file, add the synchronized database test, restart MySQL, and run the Master command:show master status
As follows:
Correspondingly, the Slave database information to be modified is added in my. cnfreplicate-do-db=test
Restart Mysql and execute the following content in the Slave database according to the above show master status:
>stop slave>change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000005', master_log_pos=120;>start slave
Then use: show slave status;
mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.1 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 1422 Relay_Log_File: VM_128_194_centos-relay-bin.000004 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ufind_db,test Replicate_Ignore_DB: mysql 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: 1422 Relay_Log_Space: 468 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: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd Master_Info_File: /data/mysqldb/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)ERROR: No query specifiedmysql>
Test has been added.
Real Testing
Add the database table and user to the master database. Observe the slave database changes as follows:
When creating a database:
When adding data:
When deleting a Master database table:
So far, I hope to help you with the configuration process. If you have any questions, please leave a message.