Configuring mysql5.5 Master-Slave server (RPM)

Tutorial Start:
First, install MySQL

Description: Install MySQL 5.5.22 on two MySQL servers 192.168.21.169 and 192.168.21.168, respectively

Second, configure MySQL master server (192.168.21.169)
Mysql-uroot-p #进入MySQL控制台
Create Database Osyunweidb; #建立数据库osyunweidb
Insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' osyunweiuser ', Password (' 123456 ')); #创建用户osyunweiuser

#建立MySQL主从数据库同步用户osyunweidbbak密码123456

Flush privileges; #刷新系统授权表

#授权用户osyunweidbbak只能从192.168.21.168 This IP accesses the primary server 192.168.21.169 the database above and has only the permissions of the database backup
Grant Replication Slave on * * to ' osyunweidbbak ' @ ' 192.168.21.168 ' identified by ' 123456 ' with GRANT option;

Third, the MySQL master server 192.168.21.169 in the database osyunweidb imported to MySQL from the server 192.168.21.168
1. Export Database Osyunweidb

Mysqldump-u root-p osyunweidb >/home/osyunweidbbak.sql #在MySQL主服务器进行操作, Export database osyunweidb to/home/osyunweidbbak.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; #解除锁定

2. Import database to MySQL from server

Mysql-u root-p #进入从服务器MySQL控制台

Create Database Osyunweidb; #创建数据库

Use Osyunweidb #进入数据库

Source/home/osyunweidbbak.sql #导入备份文件到数据库

Mysql-u osyunweidbbak-h 192.168.21.169-p #测试在从服务器上登录到主服务器
iv. Configuring the MySQL master server 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=osyunweidb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Binlog-ignore-db=mysql #不同步mysql系统数据库
Service mysqld Restart #重启MySQL
Mysql-u root-p #进入mysql控制台
Show master status; To view the primary server, a similar message appears
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|    mysql-bin.000019 | 7131 | Osyunweidb | MySQL |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: The value of file is remembered here:mysql-bin.000019and the value of position:7131, which will be used later.
v. Configure MySQL MY.CNF files 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=osyunweidb #需要同步的数据库名, 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控制台
Slave stop; #停止slave同步进程
Change Master to master_host= ' 192.168.21.169 ', master_user= ' Osyunweidbbak ', master_password= ' 123456 ', Master_log_    File= ' mysql-bin.000019 ', master_log_pos=7131; #执行同步语句
Slave start; #开启slave同步进程
Show SLAVE Status\g #查看slave同步信息, the following appears
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.21.169
Master_user:osyunweidbbak
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:osyunweidb
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!
 

Six, test MySQL master server dual-Machine hot standby is successful
1. Go to MySQL master server

Mysql-u root-p #进入主服务器MySQL控制台

Use Osyunweidb #进入数据库

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 Osyunweidb #进入数据库

Show tables; #查看osyunweidb表结构, 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

Configuring mysql5.5 Master-Slave server (RPM)