Configuring the mysql5.5 master-slave server

First, the Environment preparation

MySQL version is 5.5.22

192.168.21.169 MySQL master server

192.168.21.168 mysql Slave server

Second, configure MySQL master server (192.168.21.169)

Mysql-uroot-p #进入MySQL控制台create database osyunweidb;   #建立数据库osyunweidbinsert into Mysql.user (Host,user,password) VALUES (' localhost ', ' osyunweiuser ', Password (' 123456 '));   #创建用户osyunweiuser flush Privileges; #刷新系统授权表grant replication Slave on * * to ' osyunweidbbak ' @ ' 192.168.21.168 ' identified by ' 123456 ' with GRANT option; #建立MySQL主从数据库同步用户, authorized user Osyunweidbbak can only access the primary server 192.168.21.169 the above database from 192.168.21.168 this IP, and only have database backup permissions

Third, configure the MySQL master server my.cnf file

Vi/etc/my.cnf #编辑配置文件, in the [Mysqld] section add the following server-id=1 #设置服务器id, for 1 to indicate the primary server, note: If the original configuration file already has this line, there is no need to 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 more than one database, repeat this parameter for each database row Binlog-ignore-db=mysql #不同步mysql系统数据库

Service mysqld Restart #重启MySQLmysql-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.

Iv. configuring MySQL my.cnf files from the server

VI/ETC/MY.CNF #编辑配置文件, add the following in the [mysqld] section server-id=2 #配置文件中已经有一行server-id=1, and modify its value to 2, expressed as Log-bin=mysql-bin from the database # Start the MYSQ binary logging system, note: If the original configuration file already has this line, there is no need to add. Replicate-do-db=osyunweidb #需要同步的数据库名, if you have more than one database, repeat this parameter for each database row Replicate-ignore-db=mysql #不同步mysql系统数据库

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!
 

Five, 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

Configuring the mysql5.5 master-slave server