Let's talk about how to build a MySQL Replication environment.

This article mainly introducesBuild a MySQL Replication EnvironmentTo achieveDatabase Load Balancing. The specific process of setting up the MySQL Replication environment is as follows:

Environment: Two ubuntu instances, one Master instance and one Slave instance.

Master: ubuntub (192.168.1.101)

Slave: ubuntuc (192.168.1.104)

The configuration on the Master is as follows:

 
 

  
  
Ubuntub @ ubuntub:/etc/mysql $ sudo vi my. cnf
  
  
 
  
  
# Bind-address = 127.0.0.1 // comment out
  
  
 
  
  
Server-id = 1 // set master to 1 and slave to 2
  
  
 
  
  
Log_bin =/var/log/mysql/mysql-bin.log
  
  
 
  
  
Ubuntub @ ubuntub:/etc/mysql $ sudo/etc/init. d/mysql restart
  
  
 
  
  
Ubuntub @ ubuntub:/etc/mysql $ mysql-uroot-p
  
  
 
  
  
Mysql> grant replication slave on *. * TO 'rep _ user' @ '% 'identified BY 'rep _ password ';
  
  
 
  
  
Mysql> flush tables with read lock;
  
  
 
  
  
Query OK, 0 rows affected (0.00 sec)
  
  
 
  
  
Mysql> show master status;
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
| Mysql-bin.000001 | 261 |
  
  
 
  
  
|
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
1 row in set (0.02 sec)
  
  
 
  
  
Mysql> use rep;
  
  
 
  
  
Mysql> select * from user;
  
  
 
  
  
+ ------- + ---- +
  
  
 
  
  
| Name | id |
  
  
 
  
  
+ ------- + ---- +
  
  
 
  
  
| B | 1 |
  
  
 
  
  
| User3 | 2 |
  
  
 
  
  
+ ------- + ---- +
  
  
 
  
  
2 rows in set (0.00 sec)
  
  
 
  
  
Ubuntub @ ubuntub:/data/mysql_bak $ mysqldump-uroot-p rep> dbdump. SQL
  
  
 
  
  
Ubuntub @ ubuntub:/data/mysql_bak $ mysql-u root-p
  
  
 
  
  
Mysql> unlock tables;
  
  
 
  
  
Ubuntub @ ubuntub:/data/mysql_bak $ scp/data/mysql_bak/dbdump. SQL ubuntuc@192.168.1.104:/data // reverse the backup data to the slave
 
 

Configurations on Slave:

 
 

  
  
Ubuntuc @ ubuntuc:/data $ sudo vi/etc/mysql/my. cnf
  
  
 
  
  
# Bind-address = 127.0.0.1
  
  
 
  
  
Server-id = 2
  
  
 
  
  
Log_bin =/var/log/mysql/mysql-bin.log
  
  
 
  
  
Ubuntuc @ ubuntuc:/etc/mysql $ sudo/etc/init. d/mysql restart
  
  
 
  
  
Ubuntuc @ ubuntuc:/data $ mysql-u root-p
  
  
 
  
  
Mysql> use rep;
  
  
 
  
  
Mysql> source/data/dbdump. SQL // import data
  
  
 
  
  
Mysql> change master to // Change Master
  
  
 
  
  
-> MASTER_HOST = '192. 168.1.101 ',
  
  
 
  
  
-> MASTER_USER = 'rep _ user ',
  
  
 
  
  
-> MASTER_PASSWORD = 'rep _ password ',
  
  
 
  
  
-> MASTER_LOG_FILE = 'mysql-bin.000001 ',
  
  
 
  
  
-> MASTER_LOG_POS = 261;
  
  
 
  
  
Mysql> start slave;
  
  
 
  
  
Mysql> show master status;
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
| Mysql-bin.000002 | 746 |
  
  
 
  
  
|
  
  
 
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
 
  
  
Mysql> show slave status; // check whether there are any errors. For example, if server-id conflicts, some errors may occur.
 
 

Test:

1) Create a database on the Master and view it on the Slave. You can see the show databases;

2) operate the user table of the rep database on the Master, and immediately copy the result to the slave;

This section describes how to build a MySQL Replication environment.