Master-slave settings for MySQL 5.5 + (master-slave settings after MySQL and settings before MySQL)

Mysql5.6 + Master/Slave settings (there are some gaps between the Master/Slave settings after mysql5.5 and those before 5.5, follow these steps to first enable the database port firewalls between the master and slave mysql servers before configuring the master and slave databases.

Mysql5.6 + Master/Slave settings (there are some gaps between the Master/Slave settings after mysql5.5 and those before 5.5, follow these steps to first enable the database port firewalls between the master and slave mysql servers before configuring the master and slave databases.

Mysql5.6 + Master/Slave settings (the Master/Slave settings after mysql5.5 differ from those before MySQL 5.5)

How to install the mysql database? I will not talk about it here, but about its master-slave replication. The steps are as follows:

Before configuring the master-slave settings

First, make sure that the database port firewalls between mysql master and slave servers are opened to each other,

Make sure that the master-slave database account is consistent (used for master-slave switchover). Otherwise, the Operation will fail,

The second is to ensure that the mysql account has the "writable" permission on the mysql database directory, not the "writable" permission,

To ensure that there is no accident, it is best to delete the old mysql-bin and mysql logs before mysql, and then restart mysql

Create a new new_test database on the master server and create a test table for it, and then export the database to the slave server (test Master/Slave usage) the server can be replaced with an existing database for operations (I have created a master-slave instance with the same account, password, and port configuration. For master-slave switchover, it is easy to test, remember that the firewall port and log location must have the read and write permissions)

1. The master and slave servers perform the following operations respectively:
1.1 consistent version
1.2 initialize the table and start mysql in the background
1.3 modify the root password

2. Modify the master server:
# Vi/etc/my. cnf

[Mysqld]
Log-bin = mysql-bin // [required] Enable binary log
Server-id = 1 // [required] The default value is 1. Generally, the last segment of the IP address is used.

Port = 1223
Bing-address = 0.0.0.0

Log-bin = mysql-bin.log (required, Database log File, Master/Slave required)
Binlog-do-db = new_test (for the database to be recorded, multiple newlines can be set multiple times)
Replicate-do-db = new_test (for the database to be copied, you can set multiple newlines)

Binlog-ignore-db = mysql // do not record the mysql database as follows:
Binlog-ignore-db = test
Binlog-ignore-db = information_schema
Binlog-ignore-db = performance_schema

Replicate-ignore-db = test // do not copy test as follows
Replicate-ignore-db = mysql
Replicate-ignore-db = information_schema
Replicate-ignore-db = performance_schema

3. Modify the slave server slave:
# Vi/etc/my. cnf
[Mysqld]
Port = 1223
Bing-address = 0.0.0.0 // This means that you can set the Server IP address to 116.128.1.10 for the sake of security of all servers.
Log-bin = mysql-bin.log
Server-id = 2
Binlog-do-db = new_test
Replicate-do-db = new_test

Binlog-ignore-db = mysql
Binlog-ignore-db = test
Binlog-ignore-db = information_schema
Binlog-ignore-db = performance_schema

Replicate-ignore-db = test
Replicate-ignore-db = mysql
Replicate-ignore-db = information_schema
Replicate-ignore-db = performance_schema
4. Restart the mysql of the two servers.
Service mysql restart

5. Create an account on the master server and authorize slave:
#/Usr/local/mysql/bin/mysql-u database account name-p Database Password
Mysql> grant replication slave on *. * to 'newback _ username' @ '%' identified by 'newback _ pwd'; // generally, the root account is not used, "%" indicates that all clients may be connected (for security reasons, replace % with the specified server IP address, such as 116.121.1.10). If the account and password are correct.

6. log on to mysql on the master server and query the master Status (operations can be performed in phpmyadmin)
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 120 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.

7. Configure Slave server Slave:
Mysql> change master to master_host = '2017. 121.1.10 ', master_port = 1223, master_user = 'newback', master_password = 'CCTV @ 12315 # $', master_log_file = 'mysql-bin.000001 ', master_log_pos = 120 // do not disconnect, master_port is the mysql server port number (without quotation marks), master_user is the database account for performing synchronization operations, and "120" is not single quotation marks (120 here is the position value seen in show master status, the mysql-bin.000001 here is the value corresponding to the file ). (You can use SQL statements on the slave server phpmyadmin)

Mysql> start slave; // start the slave Server replication function (you can execute this SQL statement in phpmyadmin)

8. Check the slave server copy function status:
Mysql> show slave status \ G (you can run the "show slave status" SQL statement in phpmyadmin)
* *************************** 1. row ***************************

........................ (Omitted)
Slave_IO_Running: Yes // This status must be YES
Slave_ SQL _Running: Yes // This status must be YES
........................ (Omitted)

Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. Otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).

The master and slave servers are configured.

9. Master/Slave server test:
Insert or update a record in the test table of the master server new_test database. If the slave server also updates the insert record, the configuration is correct; otherwise, an error occurs.

10. Differences between MySQL + and earlier MySQL versions:

Most of the content is similar to that after 5.5, master-host, master-user, master-password, and master-port are no longer supported. ERROR: [ERROR]/usr/local/mysql/bin/mysqld: unknown variable 'master-host = 192.168.2.182' Master ConfigurationUnchanged. It is still server-id = 1log-bin = logbinlog-do-db = database1 // The database binlog to be synchronized-do-db = database2binlog-ignore-db = mysql // Ignored database ............ (Omitted) Slave ConfigurationChange to (note that all the following comments are discarded ): server-id = 2 # master-host = 192.168.124.51 # master-user = AffairLog # master-password = password # master-port = 3306 # master-connect-retry = 60replicate-do-db = database1 // Synchronized database replicate-do-db = database2replicate-ignore-db = mysql // The second reason is that the ignored database cannot directly use the slave start or start slave command, because it will report an error, we need to use change master: mysql> change master to> master_host = '2017. 168.124.51 ',> master_user = 'username',> master_password = 'Password',> master_log_file = 'bin-log.000001',> master_log_pos = 120; then start slave; (You can also execute the statement in one sentence, for example, "change master to master_host = '2017. 121.1.10 ', master_port = 1223, master_user = 'newback', master_password = 'CCTV @ 12315 # $', master_log_file = 'mysql-bin.000001 ', master_log_pos = 120 "[The actual command removes the double quotation marks, and the port number and master_log_pos are not enclosed.]) Everything else remains unchanged.