Ubutun 14.10 MySQL Master-slave synchronization detailed operation

First, the relevant introduction

1 Master-slave replication, is used to establish a database and the main database exactly the same as the databases environment, called from the database, the main database is generally real-time business database, from the role of the database and the use of a few general:
First, as a backup database, the primary database server failure, you can switch to continue to work from the database;
Second, the database can be used for backup, data statistics and so on, so as not to affect the performance of the main database;
2 Read and write separation, refers to the reading and writing respectively using different databases, of course, usually on different servers; the read and write environment on the same server is estimated to be used for testing.
Generally read and write database environment is configured, a write database, one or more read database, each database on different servers, take full advantage of server performance and database performance, of course, it will involve how to ensure that the data read-write database consistency, this can be done using master-slave replication technology.
The general application is: The business throughput is very large, read the database (can be easily understood as the proportion and impact of the SELECT statement) the load is large;
The official mysql-proxy is a software that implements multiple functions such as read-write separation, load balancing, and so on.

Ii. Methods of operation
1. Install MySQL
Description: Install MySQL 5.5.22 on two MySQL servers 192.168.45.127 and 192.168.45.128, respectively

2. Configure MySQL master server (192.168.45.127)
Mysql-uroot-p #进入MySQL控制台
Create Database Takeweb; #建立数据库talkweb
Insert into Mysql.user (Host,user,password)/
VALUES (' localhost ', ' testuser ', password (' 123456 ')); #创建用户testuser
If error: Use Create USER ' testuser ' identified by ' 123456 ';

#建立MySQL主从数据库同步用户testuser密码123456
Flush privileges; #刷新系统授权表
#授权用户talwebbak只能从192.168.45.128 This IP accesses the primary server 192.168.45.127 the database above and has only the permissions of the database backup
Grant Replication Slave on * * to ' talkwebbak ' @ ' 192.168.45.128 ' identified by ' 123456 ' with GRANT option;

3. Import the database talkweb from MySQL master server 192.168.45.127 to MySQL from the server 192.168.45.128

A) Export database Talkweb
Mysqldump-u root-p talkweb >/home/talkwebbak.sql #在MySQL主服务器进行操作, Export database Talkweb to/home/talkwebbak.sql
Note: Before exporting, you can go to the MySQL console and execute the following command
Flush tables with read lock; #数据库只读锁定命令 to prevent data from being written when the database is exported
Unlock tables; #解除锁定
b) Import database to MySQL from server
Mysql-u root-p #进入从服务器MySQL控制台
Create Database Talkweb; #创建数据库
Use Talkweb #进入数据库
Source/home/talkwebbak.sql #导入备份文件到数据库
Mysql-u talkwebbak-h 192.168.45.127-p #测试在从服务器上登录到主服务器

c) Configuring the MySQL master server's my.cnf file
vi/etc/my.cnf #编辑配置文件, add the following in the [Mysqld] section
server-id=1 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.
Log_bin=mysql-bin #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore.
Binlog-do-db=talkweb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Binlog-ignore-db=mysql #不同步mysql系统数据库
Service mysqld Restart #重启MySQLd
mysql-u root-p #进入mysql控制台
Show master status; View the primary server with the following similar information
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 7131 | talkweb | mysql |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: The value of file is remembered here: the value of mysql-bin.000019 and position: 7131, which is used later.

d) Configure the MySQL my.cnf file from the server
vi/etc/my.cnf #编辑配置文件, add the following in the [Mysqld] section
server-id=2 #配置文件中已经有一行server-id=1, modifying its value to 2, expressed as a slave database
Log_bin=mysql-bin #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore.
Replicate-do-db=talkweb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Replicate-ignore-db=mysql #不同步mysql系统数据库
: wq! #保存退出
Service mysqld Restart #重启MySQL
Note: After MySQL 5.1.7, it is not supported to write the master configuration attribute to the MY.CNF configuration file, just write the synchronized database and the database to be ignored.
mysql-u root-p #进入MySQL控制台
stop slave; #停止slave同步进程
Change master to master_host= ' 192.168.45.127 ',
master_user= ' Talkwebbak ',
master_password= ' 123456 ',
master_log_file= ' mysql-bin.000019 ',
master_log_pos=7131; #执行同步语句
start slave; #开启slave同步进程
show SLAVE Status\g #查看slave同步信息, the following appears
*************************** 1. Row ***************************
slave_io_state:waiting for Master to send event
master_host:192.168.45.127
Master_user:talkwebbak
master_port:3306
connect_retry:60
master_log_file:mysql-bin.000019
read_master_log_pos:7131
relay_log_file:mysqlslave-relay-bin.000002
relay_log_pos:253
relay_master_log_file:mysql-bin.000019
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:talkweb
Replicate_ignore_db:mysql
replicate_do_table:
replicate_ignore_table:
1 row in Set (0.00 sec)
Note check:
Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means the configuration was successful!

e) test MySQL master server dual machine hot standby is successful
1. Go to MySQL master server
Mysql-u root-p #进入主服务器MySQL控制台
Use Talkweb #进入数据库
CREATE TABLE Test (ID int not NULL primary key,name char (20)); #创建test表
2. Enter MySQL from the server
Mysql-u root-p #进入MySQL控制台
Use Talkweb #进入数据库
Show tables; #查看talkweb表结构, you see a new table test that indicates that the database synchronization was successful
At this point, the MySQL database configuration master-slave server to implement a two-machine hot standby Example tutorial completed

Ubutun 14.10 MySQL Master-slave synchronization detailed operation