mysql主从配置 [ 未指定版本 ]

原文链接

About MySQL replication(关于主从)

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses:

12.34.56.789- Master Database

12.23.34.456- Slave Database

Setup 首先安装mysql

sudo apt-get install mysql-server mysql-client

Step One—Configure the Master Database(打开配置文件)

Open up the mysql configuration file on the master server.

sudo nano /etc/mysql/my.cnf

将bind-address从127.0.0.1换成12.34.56.789
Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host:

bind-address            = 127.0.0.1

Replace the standard IP address with the IP address of server.

bind-address            = 12.34.56.789 

设置一个服务器id
The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1.

Make sure this line is uncommented.

server-id               = 1

设置bin log 文件,要注意文件权限
Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

log_bin                 = /var/log/mysql/mysql-bin.log

设置log的数据库,同时也是需要主从同步的数据库
Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

binlog_do_db            = newdatabase

重启mysql,如果启动失败,注意看mysql error log, 一般就是常见的是文件权限的问题
After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

sudo service mysql restart

命令行连接mysql
The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell.

mysql -u root -p

配置一个帐号,用于主从同步
We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

查看master的状态, 记住file和position,后面会用到
You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

导出需要主从同步的数据库
This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

mysqldump -u root -p --opt newdatabase > newdatabase.sql
UNLOCK TABLES;
QUIT;

主mysql已经配置完成, 接下来是从mysql的配置过程了

Step Two—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

创建数据库
Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

CREATE DATABASE newdatabase;
EXIT;

导入sql文件,该sql文件就是你刚才从主mysql导出来的
Import the database that you previously exported from the master database.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

打开配置文件
Now we need to configure the slave configuration in the same way as we did the master:

sudo nano /etc/mysql/my.cnf

设置id
We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

server-id               = 2

log配置
Following that, make sure that your have the following three criteria appropriately filled out:

relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase

重启mysql
You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

sudo service mysql restart

到mysql客户端,输入下面命令,注意ip,user,password,master_log_file,master_log_pos,后面两个参数,就是前面show master status看到的数据
The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:

• It designates the current server as the slave of our master server.
• It provides the server the correct login credentials
• Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

启动slave
With that—you have configured a master and slave server

START SLAVE;`

查看slave的status, 如果有问题会在error相关的字段里面有提示,注意看,如果没有问题,Read_Master_Log_Pos与master的show master status看到的一致
You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G