MYSQL master-slave synchronization configuration for CentOS

MYSQL master-slave synchronization configuration for CentOS

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;