Note on setting up the MySQL replication Environment

MySQL replication is a simple and common database load balancing method.

Here we record the specific process of Environment setup:

Environment: Two Ubuntu instances, one master instance and one slave instance.

MASTER: ubuntub (192.168.1.101)

SLAVE: ubuntuc (192.168.1.104)

 

On the master:

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

 

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;

References:

Http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/

Http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html