Linux MySQL master-slave Replication (Replication) Configuration

MySQL is an open-source relational database system. Replication is a process that replicates data from a MySQL database server (master server) to another server (slave server slave.
 
Configure the master server)
 
1. Edit the database configuration file my. cnf, which is usually in the/etc/directory.
 
1. # vi/etc/my. cnf
 
Add the following code under [mysqld:
 
1. log-bin = mysql-bin
2. server-id = 1
3. innodb_flush_log_at_trx_commit = 1
4. sync_binlog = 1
5. binlog-do-db = wordpress
6. binlog_ignore_db = mysql
 
1 In server-id = 1 can be defined, as long as it is unique.
Binlog-do-db = wordpress indicates that only wordpress is backed up.
Binlog_ignore_db = mysql indicates that the backup mysql is ignored.
If binlog-do-db and binlog_ignore_db are not added, all databases are backed up.
2. Restart MySQL:
 
1. # service mysqld restart
 
3. log on to the MySQL server.
 
1. # mysql-uroot-p
 
Create a new user on the master server to grant the "replication slave" permission. You do not need to grant other permissions. In the following command, replace X. X with the IP address of the slave server.
 
1. mysql> create user 'user' @ 'x. X. X. X' identified by 'Password ';
2. mysql> grant replication slave on *. * TO 'user' @ 'x. X. X. X' identified by 'Password ';
 
4. Run the following command to lock the database to prevent data writing.
 
1. mysql> flush tables with read lock;
 
5. Exit mysql command line and export the database
 
1. # mysqldump-u root-p123456 -- all-databases -- lock-tables = false -->/root/all. SQL
 
6. Use the scp command to transmit the database file all. SQL to the slave server.
 
1. # scp/root/all. root@www.example.com:/root
 
7. Connect to the database again and go to the mysql command line to check the master status.
 
1. mysql> show master status;
 
Please note the information that is displayed, which will be used by the configuration slave server.
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000003 | 1001741 | dbispconfig | mysql |
+ ------ + ---- + ----- + ------ +
1 row in set (0.00 sec)
8. Unlock the data table.
 
1. mysql> unlock tables;
 
Configure slave server (slave)
 
Log on to the slave server.
1. Import the database of the master server.
 
1. # mysql-u root-p123456 </root/all. SQL
 
2. Edit the configuration file my. cnf and add it under [mysqld:
 
1. server-id = 2
 
2. You can define it as long as it is unique.
3. Save the file and restart mysqld.
 
1. # service mysqld restart
 
4. log on to the mysql server and run the following command.
 
1. mysql> CHANGE MASTER
2. MASTER_HOST = 'x. X. X. x ',
3. MASTER_USER = 'user ',
4. MASTER_PASSWORD = 'Password ',
5. MASTER_PORT = 3306,
6. MASTER_LOG_FILE = 'mysql-bin.000001 ',
7. MASTER_LOG_POS = 98,
8. MASTER_CONNECT_RETRY = 10;
 
MASTER_HOST: IP address of the master server.
MASTER_USER: username created when configuring the master server
MASTER_PASSWORD: User Password
MASTER_PORT: the mysql port of the master server. If it has not been modified, the default value is enough.
5. Start the slave process.
 
1. mysql> start slave;
 
6. view the mysql log, which is usually in mysqld. log under the/var/log/directory. If the startup is successful, you should see a log similar to the following.
 
[Root @ localhost ~] # Vi/etc/my. cnf

091104 8:42:02 [Note] Slave I/O thread: connected to master 'root @ X. X: 3306 ?, Replication started in log 'mysql-bin.000001? At position 98
Now the master server and slave server are configured. In addition, you may need to set the expiration time of the database binary log of the master server. You can use the expire_logs_days parameter in the configuration file to set the expiration time.