Mysql主从同步实现教程

启动 service mysqld restart

mysqld_safe &

源码 ：

./configure --prefix=/usr/local/mysql

make

make install

初始化密码：用mysqladmin

mysqladmin -u root password “newpass”

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass “newpass”

实现原理：

主服务器将更新写入二进制日志文件binlog，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后*并等待主服务器通知新的更新。

增量更新

复制如何工作：

(1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件，binary log events);

(2) slave将master的binary log events拷贝到它的中继日志(relay log);

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

主从复制解决的问题：

数据分布

负载平衡

备份

高可用性和容错性

mysql不同版本之间能否进行数据的同步?

可以，但可能出现同步的不稳定(兼容性问题)，可能会在某些函数处理、日志读取、日志的解析重演上等上发生异常，导致同步报错而需手工处理。

官方建议 版本保持一致，slave至少要和master的版本前两个号相同，可高于master，版本向前兼容

引擎不一致可以吗?：

可以，bin-log存储的是sql语句，增删改是没问题的，一般主库使用innodb，从库使用myisam，做读写分离。

做主从一定要两台机器吗?

只要有两个或以上的mysql服务即可，可以在同一台机器上启动多个mysql服务

部署方案： 一主多从 主主互备

实验流程：主服务器一般有数据的情况扩展成主从复制

<1> 创建复制账号

<2> 配置主库和从库

<3> 启动复制

利用两个虚拟机，安装好mysql(版本5.1以上)，指定主服务器(192.168.1.241)和从服务器(192.168.1.242)

1. 在主服务器上创建账号用于备份

create user backuser

grant replication slave on *.* to backuser@‘192.168.1.134’identified by ‘123456’

grant all privileges on *.* to user1@192.168.1.134 identified by '123456'

grant ： 授权

replication slave ，all privileges 操作动作 select update delete

on 作用范围

*： 库

*： 表

to：指定账号

user1，backuser：账号

@ 接 访问来源

192.168.134 允许从192.168.1.%用backuser连接

identified by ： 设置密码

查询用户验证：

select distinct user from mysql.user

show grants for backuser@'192.168.1.242'

2. 配置文件查看 /etc/my.cnf

[mysqld]

batadir : 数据文件保存目录

socket: 用于客户端与服务器端通信的socket文件

[mysqld-safe]

log_error : 错误日志记录文件

pid_file : 进程ID保存的文件

3. 创建一个数据库php1503，数据表test，字段有：id stuname age 。。。

create database php1503;

create table test(

id int auto_increment comment '主键id',

stuname varchar(16) not null comment '姓名',

age tinyint not null default 0 comment '年龄'

primary key(id)

)engine=innodb default charset=utf8 auto_increment=1

查看sql语句：show create table test;

查看表结构 desc test 或者 desc test\G; \G表示行列倒置显示

查看索引 show indexes from test;

4. 添加数据

insert into test(stuname,age) values('张三',18),('李四',20),('王五',24);

查询 select * from test;

5. 备份已有的数据

mysqldump -uroot -p123 php1503 -l -F > /tmp/test.sql

-l : 读锁

-F : flush logs，重新生成新的日志文件，包括log-bin日志

6. 将test.sql拷贝到从服务器，并恢复到数据库

scp /tmp/test.sql root@192.168.1.242:/tmp

从库创建php1503数据库，并恢复文件

create database php1503;

mysql -uroot -p123456 php1503 -v -f < /tmp/test.sql;

-v 查看导入的详细信息

-f 遇到错误可以跳过，继续执行下面的语句

7. 主库是否开启binlog

show master status

查看/var/lib/mysql 下是否存在mysql-bin.000001 和 mysql-bin.index文件

8. 修改主库的/etc/my.cnf，开启binlog配置

[mysqld]

log_bin=mysql-bin

#启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。

binlog-do-db=php1503 #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

binlog-ignore-db=mysql #不同步mysql系统数据库

server_id = 1 有些mysql版本根本不允许使用1，建议修改成其他数字

#设置服务器id

character-set-server=utf8

重启主库 service mysqld restart / pkill mysqld && mysqld_safe

9. 查看binlog文件

mysqlbinlog mysql-bin.000001

10 配置从服务器的/etc/my.cnf

log_bin = mysql-bin 可以不开启

server_id = 12 // 必须的

relay_log = mysql-relay-bin // 中继日志

log_slave_updates = 1 // slave将复制事件写进自己的二进制日志

read_only = 1

旧版本额外配置：

log_bin=mysql-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。

replicate-do-db=php1503

replicate-ignore-db=mysql

read_only=1

server_id = 12

character-set-server=utf8

master-host = 192.168.1.241

master-user = backuser

master-password = 123456

master-port = 3306

重启服务器

11 启动复制，告诉备库如何连接主库并重放二进制日志

mysql中执行

change master to master_host='192.168.1.241', master_user='bakuser',master_password='123456',master-port='3306',master_log_file='mysql-bin.000001',master_log_pos=0;

master_log_pos=0 表示日志从头开始读起

12. 检查复制是否正确执行

show slave status

Slave_IO_State Slave_IO_Running Slave_SQL_Running

13. 开始复制

start slave

show slave status

查看线程 show processlist\G

14. 在主库添加数据，查看从库的变化

备份方法：

1. 冷备份 关闭主库，把数据复制到备库

2. 热备份 如果是myisam表，可以使用mysqlhotcopy或rsync复制数据

3. mysqldump

<1>锁表

mysql> FLUSH TABLES WITH READ LOCK;

<2>在另一个连接用mysqldump创建一个你想进行复制的数据库的转储：

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

shell> mysqldump -uroot -p123 php1503 -l -F > /tmp/test.sql

<3>对表释放锁。

mysql> UNLOCK TABLES;

4. binlog恢复

mysqlbinlog --no-defaults binlog-file | mysql -uroot -p123456

恢复binlog-file二进制日志文件

mysqlbinlog --no-defaults --start-position=‘500’--stop-position=‘644’ mysql-bin.000004 | mysql -uroot -p123456 t1

reset master 清除所有日志