Linux MySQL master-slave Replication (Replication) Configuration

Source: Internet
Author: User
Tags mysql command line scp command

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.