mysql主从复制

原创

wangdi250 2015-04-16 14:53:20 博主文章分类：mysql ©著作权

文章标签 服务器 database create 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者wangdi250的原创作品，请联系作者获取转载授权，否则将追究法律责任

今天做了一下mysql主从复制的实验，主服务器主机名为master,从服务器的主机名为slave,整个过程一般分为以下几个步骤进行：

1、在主服务器上开启二进制日志功能，设置唯一的服务器ID编号，这些设置需要重启MYSQL服务

2、在所有的从服务器上设置唯一的服务器ID编号，这些设置需要重启MYSQL服务

3、在主服务器上为不用的从服务器创建可以读取主服务器日志文件的账户，或使用相同的统一账户

4、在进行数据复制之前，你还需要记录主服务器上二进制日志的位置标记

一．数据复制环境中主服务器的设置

create database hr;

use hr;

create table employees(

employee_id INT NOT NULL AUTO_INCREMENT,

name char(20) NOT NULL,

e_mail varchar(50),

PRIMARY KEY(employee_id));

insert into employeesvalues(1,'TOM','tom@example.com');

insert into employeesvalues(2,'Jerry','jerry@example.com');

然后需要在主服务器上开启二进制日志并设置服务器编号，服务器唯一编号必须是1-2的32次方-1之间的整数，根据自己的实际情况进行设置。进行这些设置需要关闭MYSQL数据库并编辑my.cnf文件，然后在[mysqld]设置段添加相应的配置选项。

[mysqld]

server-id = 1

log-bin=mysql-bin

[root@master ~]# service mysqld restart

[root@master ~]# service iptables stop

2、数据复制环境中从服务器的设置

如果从服务器ID编号没有设置，或服务器ID编号与主服务器有冲突，就必须关闭MYSQL服务，并重新编辑配置文件，设置唯一的服务器编号，最后重启MYSQL服务。如果有多台从服务器，则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联，这样ID编号可以同时唯一表示一台服务器计算机

[mysqld]

server-id = 2

log-bin=mysql-bin

[root@master ~]# service mysqld restart

[root@master ~]# service iptables stop

对复制而言，MYSQL从服务器上二进制日志功能是不需要开启的。但是，你也可以通过启动从服务器的二进制日志功能，实现数据备份与恢复。此外，在一些更复杂的拓扑环境中，MYSQL从服务器也可以扮演其他从服务器的主服务器

3、创建复制账号

执行数据复制时，所有的从服务器都需要使用账户与密码链接MYSQL主服务器，所以在主服务器上必须存在至少一个用户账户及相应的密码供从服务器连接。这个账户必须拥有REPLICATION SLAVE权限，你可以为不同的从服务器创建不同的账户与密码，也可以使用统一的账户密码。如果该用户仅为数据库复制所使用，则该账户仅需要REPLICATION SLAVE权限即可。下面的例子将在MYSQL主服务器上创建一个拥有复制权限的slave_cp账户，密码为slaveadmin

GRANT REPLICATION SLAVE ON *.* TO'slave_cp'@'%' IDENTIFIED BY 'slaveadmin';

这里的%代表所有主机都通过slave_cp能访问服务器，这在生产环境中不允许，应注意

获取主服务器二进制日志信息

在进行主从数据复制之前，我们来了解一些主服务器的二进制日志文件的基本信息，这些信息在对从服务器的设置中需要用到，它包括主服务器二进制文件名称及当前日志记录位置，这样从服务器就可以知道从哪里开始进行复制操作。

我们可以使用如下操作查看主服务器二进制日志数据信息

FLUSH TABLES WITH READ LOCK;

show binary logs; show master status\G

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 1583 |

+------------------+-----------+

UNLOCK TABLES；

FLUSH TABLES WITH READ LOCK命令的作用是对所有数据库的表执行只读锁定，只读锁定后所有数据库的写操作将被拒绝，但读操作可以继续。执行锁定可以防止在查看二进制日志信息的同时有人对数据进行修改操作，最后使用UNLOCK TABLES语句对全局锁执行结束操作。

5.对现有数据库进行快照备份

如果在使用二进制日志进行数据复制以前，MYSQL数据库系统中已经存在大量的数据资源，对这些资料进行数据备份的一种方法是使用mysqldump工具，在主服务器上使用该工具对数据备份后，即可在从服务器上进行数据还原操作。当希望的数据达到主从一致后，就可以使用数据库复制功能进行自动同步操作。

[root@master ~]# mysqldump --all-databases--lock-all-tables > /tmp/dbdump.sql

[root@master ~]# scp /tmp/dbdump.sql10.10.10.130:/tmp

[root@slave ~]# mysql -u root -p123456< dbdump.sql

6.配置从服务器连接主服务器进行数据复制

数据复制的关键操作是配置从服务器去连接主服务器进行数据复制，我们需要告知从服务器建立网络连接所有必要的信息。使用CHANGE MASTER TO 语句即可完成该项工作，MASTER_HOST指定主服务器主机名或IP地址，MASTER_USER为主服务器上创建的拥有复制权限的账户名称，MASTER_PASSWORD为该账户的密码，MASTER_LOG_FILE指定主服务器二进制日志文件名称，MASTER_LOG_POS为主服务器二进制日志当前记录的位置。START SLAVE 开启从服务器功能进行主从连接，SHOW SLAVE STATUS查看从服务器状态。

change master tomaster_host='10.10.10.131', master_port=3306, master_user='slave_cp',master_password='slaveadmin', master_log_file='mysql-bin.000001',master_log_pos=1583;

start slave;

show slave status\G

7.数据同步验证

所有的主从服务器均设置完毕后，我们可以通过在主服务器上创建新的数据自恋，然后在从服务器上查看，所有的数据将自动同步

[root@master ~]#mysql -u root -p

create databasetest2;

use test2;

create tablet_table(

name char(20),

age int,

notevarchar(50));

create tablet_table( name char(20), age int, note varchar(50));