linux环境下mysql主从数据库配置（maser-slave-replication）

Mysql master/slave replication这个架构既可以支持高可用性双机备份系统的建立，也可以支持通过读写分离提高数据库写入和读出性能。

Master slave replication in mysql offload some queries from one server to other. The best advantage of master slave is to use master for all inserts and send some select queries to slave. This practice may increase speed of your application without going into optimization of all queries or buying more RAM.

 

This master slave configuration can be done in 3 follow steps like:

 

First connet to your server via putty or open terminal directly.

 

步骤一

 

Step 1: Setup Master configuration file properties

 

 Open my.cnf file:

 

vi /etc/my.cnf

 

And comment lines like below:

 

#skip-networking
#bind-address = 127.0.0.1

 

we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master)

 

log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db=database_name_to_replication#要同步复制的数据库名
server-id=1

 

restart mysql

 

service mysqld restart

 

 步骤二

 

Step 2: Setup Master configuration for replication user

 

 

 logon to mysql server

 

mysql

 

create an user with “replication slave on” privileges:

 

GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
GRANT RELOAD ON *.* TO 'replication_user'@'%';
GRANT SUPER ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

 

create replication datebase:

 

CREATE DATABASE database_name_to_replication;

 

Flush tables in our database:

 

USE database_name_to_replication;
FLUSH TABLES WITH READ LOCK;
 
SHOW MASTER STATUS;

 

you will see like this:

 

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |      106 | db to replicate| mysql            | 
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
==============================================

 

步骤三

 

 

Step 3: Setup Slave configuration file

 

 Open my.cnf file:

 

vi /etc/my.cnf

 

And commands lines like below:

 

server-id=2
master-host = 10.22.4.33#host_to_master_server主数据库ip地址
master-user = replication_user
master-password = password
master-port = 3306
master-connect-retry=60
replicate-do-db=database_name_to_replication

 

 Restart mysql: 

 

service mysqld restart

 

 Run below commands:

 

STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;

 

the output will be somthing like this:

 

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
.............

 

now we should load data from our master:

 

LOAD DATA FROM MASTER;

 

or… you can always use below command to load the initial data from master:(在windows环境下，load data from master似乎不起作用，没办法只能用以下的方法来实现)

 

mysql -usome_user_name -psome_password -Ddatabase_name_to_replication < dump_from_master_db.sql

 

 after our data is moved to slave server… we can unlock our tables.

Log in to MySQL sever (master) and:

 

USE database_name_to_replication;
UNLOCK TABLES;

 

Now just browse your database on master and slave servers. Put some new data on master database, browse the slave database.

Hey you have new data on your slave system.

All Done!
Cheers!!

注意：在做master-slave-replication时，slave端的mysql版本要大于等于master端的mysql版本，最好版本一致。