How to Create a master-slave server for mysql

There may be many ways to create a master-slave server for mysql. However, if you create a master-slave server for mysql on an installed mysql server, I am afraid it is not that easy.

The master-slave configuration of the mysql server is a very simple task. Instead of starting from scratch, it is always on the mysql server that has been installed by others. This will involve the mysql version, startup File and other issues.

But it doesn't matter. Let's clarify two points first.

1. mysql configuration file my. cnf location

2. How to start and stop mysql and find the Startup File

If you have two machines, mysql has been installed in the same version as possible, and the two machines have the same network, you can ping)

A friend said, "The slave server cannot be lower than the master server version." However, I am lower than the master server version and there is no problem.

Host A: 192.168.1.100

Slave B: 192.168.1.101

Multiple slave servers

1. log on to host A first.

Mysql> grant replication slave on *. * TO 'backup '@ '192. 168.220.135' identified by '123 ';

Grant permissions to the slave machine. If multiple slave machines exist, the slave machine is executed multiple times.

2. Open my. cnf of host A and enter

Server-id = 1 # host id, integer
Log_bin =/var/log/mysql/mysql-bin.log # Make sure this file is writable
Read-only = 0 # host, both read and write are allowed
Binlog-do-db = test # You need to back up data and write multiple rows.
Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines

3. Open my. cnf of slave machine B and enter

Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host = 192.168.1.100
Master-user = backup
Master-pass = 123456
Master-port = 3306
Master-connect-retry = 60 # Time Difference (in seconds) when the master server is disconnected from the server)
Replicate-do-db = test # only copy a database
Replicate-ignore-db = mysql # do not copy a database

4. Synchronize Databases

There are multiple ways to export the data test of host A to test. SQL first.

Then, create the database test from machine B. mysql imports test. SQL to the database test.

5. Restart mysql of host A and mysql of slave B.

6. Verification

In host A, mysql> show master status \ G;

In slave machine B, mysql> show slave status \ G;

You can see the following content:

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

You can perform INSERT, UPDATE, and DELETE operations in host A to check whether the operation has been modified in host B.

The above describes how to create a master-slave server for mysql.

Obtain the MySql time function of the current time

Mysql Aggregate functions

MySQL string case-sensitive Conversion Function

Use of MySQL concat Functions

Obtain the Automatically increasing MySQL primary key