MYSQL master-slave synchronization configuration for CentOS and centosmysql master-slave
1. Both the master and salver close the firewall iptables and execute the service iptables stop command:
Set SELINUX = disabled for The SELINUX file ,:
2. Create a database
Log on to mysql on the master and slave respectively: mysql-u root-p
Create database repl;
Create the log profile/usr/local/mysql/mysql-bin.log for MYSQL replication in the/usr/local/mysql directory and grant permissions and change the file owner:
[Root @ master mysql] touch mysql-bin.log
[Root @ master mysql] chmod 755 mysql-bin.log
[Root @ master mysql] chown-R mysql: mysql.
1. Modify the mysql configuration file my. cnf on the master machine, which is in the/etc directory.
Add the following fields in the [mysqld] configuration section:
Server-id = 1
Log-bin = log
Binlog-do-db = repl // the database to be synchronized. If no row exists, all databases are synchronized.
Binlog-ignore-db = mysql // ignored Database
Add a synchronization account for the Server Load balancer instance on the master machine
Grant replication slave on *. * to 'repl' @ '192. 100' identifiedby '123 ';
Restart the mysql service of the master machine: service mysqld restart
Use the show master status Command to view logs
Mysql> show master status;
+ ----------------- + ------------ + ------------------- + ----------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------- + ------------ + ------------------- + ----------------------- +
| Log.000003 | 98| Repl | Mysql |
+ ----------------- + ------------ + ------------------- + ----------------------- +
1 row in set (0.00 sec)
Add the synchronization account of the host salver to the host master:
Mysql> grant replication slave on *. * to 'slave '@ '192. 168.31.20.' identified by 'admin ';
Run the following command to View Details:
Add the following fields in the [mysqld] configuration section:
Server-id = 1
Log-bin = log
Binlog-do-db = repl // the database to be synchronized. If no row exists, all databases are synchronized.
Binlog-ignore-db = mysql // ignored Database
Add the following attribute items to the file:
Log-bin =/usr/local/mysql/mysql-bin.log
Binlog-ignore-db = mysql
Binlog-ignore-db = information_schema
Add a synchronization account for the Server Load balancer instance on the master machine
Grant replication slave on *. * to 'repl' @ '192. 100' identifiedby '123 ';
Restart the mysql service of the master machine: service mysqld restart
S
Select user. host from mysql. user
You can see that the backup account and settings are complete. The following table locks the database table.
Mysql lock table read-only (Other accounts cannot write tables after logging on to mysql. This prevents updates to the primary mysql table after the database is backed up, resulting in inconsistency with the contents of the slave database)
Mysql> flush tables with read lock;
E: View status:
[Root @ master mysql] mysql-u root-padmin-e "show master status"
Note: The log name (mysql-bin.000001) of the File field is the log File required for backup from the machine.
2. Modify the mysql configuration file on the server Load balancer instance.
Unlock database:
Mysql> unlock tables;
Add the following content in the [mysqld] Field
Server-id = 2
Start here
Master-host = 192.168.1.222
Master-user = repl
Masters-password = 123456
Master-port = 3306
Master-connect-retry = 60
It ends here. These parameters cannot be configured in mysql after mysql 5.1 in the database.
Replicate-do-db = repl // the database to be synchronized. If this row is left blank, all databases are synchronized.
Precautions (errors)
Mysql versions later than 5.1.7 do not support parameters similar to "master-host"
Run the following command on the slave database;
Change master to master_host = 'masterip', master_user = 'slave ', master_password = 'slvaepass ';
Change master to master_host = '10. 171.244.109 ',
Master_user = 'slave ',
Master_password = 'admin ',
Master_log_file = "mysql-bin.000006 ",
Master_log_pos = 107;
Restart the mysql instance of the Server Load balancer instance.
Log on to mysql on the server Load balancer instance
Mysql> start slave;
Mysql> show slave statusG;
If the status of Slave_IO_Running and Slave_ SQL _Running is Yes, the setting is successful.
Iv. Problems
Question 1: When I run the start slave command, the system prompts
ERROR 1200 (HY000): The server is not configured as slave; fixin config file or with change master,
Execute show slave status and prompt Empty set (0.00 sec ).
Or question 2 Slave_ SQL _Running: No
Later, we found that the slave has been enabled by default. We need to disable it before enabling it.
Execute slave stop;
Run again
Change master tomaster_host = '192. 168.1.222 ', master_user = 'repl', master_password = '000000', master_log_file = 'Log. 100', master_log_pos = 98;
Then execute slave start;
Then execute show slave statusG.
Shown as follows:
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.222
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.20.03
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 229
Relay_Master_Log_File: log.20.03
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
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: 98
Relay_Log_Space: 229
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)
The two red lines are displayed, indicating that the configuration is successful.
5. test whether the Master/Slave servers can be synchronized
Create a new table on the master server, which must be under repl data
Mysql> use repl
Database changed
Mysql> create table test (id int, namechar (10 ));
Query OK, 0 rows affected (0.00 sec)
Mysql> insert into test values (1, 'zaq ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into test values (1, 'xsw ');
Query OK, 1 row affected (0.00 sec)
Mysql> select * from test;
+ ------ +
| Id | Name |
+ ------- + ------ +
| 1 | Zaq |
| 1 | Xsw |
+ ------- + ------ +
2 rows in set (0.00 sec)
Check whether synchronization is performed on the slave server.
Mysql> use repl;
Database changed
Mysql> select * from test;
+ ------ +
| Id | Name |
+ ------ +
| 1 | zaq |
| 1 | xsw |
+ ------ +
2 rows in set (0.00 sec)
First
Flush privileges;
Then
Show grants for 'slave '@ '10. 169.6.133 ';
Database Logon
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)
Restart service mysql restart
Error: Starting MySQL .. The server quit without updating PID file ([FAILED] al/mysql/data/iZ23xrasgttZ. pid ).
Solution:
Delete the/etc/mysql folder
Modify the mstart entry in the configuration file and comment it out.
Precautions (errors)
Mysql versions later than 5.1.7 do not support parameters similar to "master-host"
Run the following command on the slave database;
Change master to master_host = 'masterip', master_user = 'slave ', master_password = 'slvaepass ';
Change master to master_host = '10. 171.244.109 ',
Master_user = 'slave ',
Master_password = 'admin ',
Master_log_file = "mysql-bin.000006 ",
Master_log_pos = 107;
Previous Reference URL:
Http://blog.csdn.net/sxhong/article/details/9212109 (set MySQL root Password under CentOS)
Http://blog.csdn.net/limingzhong198/article/details/20578821 (master-slave backup configuration of MYSQL database under CentOS)
Refer to the following URL:
Http://blog.sina.com.cn/s/blog_6eb46a7e0100wmph.html (MySQL Master/Slave synchronization configuration under centos)
Http://bbs.csdn.net/topics/380252598 (error handling)
The mysql error log is usually under the data directory under the mysql installation directory.