Configuring the mysql5.5 master-slave server

Source: Internet
Author: User

First, the Environment preparation

MySQL version is 5.5.22 MySQL master server mysql Slave server

Second, configure MySQL master server (

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 ' @ ' ' identified by ' 123456 ' with GRANT option; #建立MySQL主从数据库同步用户, authorized user Osyunweidbbak can only access the primary server the above database from 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= ' ', 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
1 row in Set (0.00 sec)
Note check:
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.