mysql主从
mysql 主从
一、原理
(1)流程
A ---> change data ---> 记录bin_log ---> transfer(传到) ----> B ---> repl_log(记录自己的从日志repl_log) ----> change data(更改自己的数据)
(2)说明
主mysql进行操作(create,update,insert,delete。。。),记录二进制log文件(bin_log )
然后把日志推到从上去,从接收到主日志后,修改自己的 replay_log
ps:mysql默认只开启错误日志,因为过多日志会影响系统的处理性能
二、实验:一台机器跑两个mysql服务(当然实际应用环境一般是两台机器分别跑mysql的)
(i)准备工作
主(master):3306 mysql
从(slave) :3307 mysql_slave
(1)删除原来已安装的mysql(系统默认rpm装了mysql)
rm -rf /usr/local/mysql
rm -rf /data/mysql
一、安装主mysql
(1)解压+移动目录
解压: tar zxvf mysql-5.1.49-linux-i686-glibc23.tar.gz
移动: mv mysql-5.1.49-linux-i686-glibc23 /usr/local/mysql
(2)检查有没有mysql用户
grep 'mysql' /etc/passwd (没有则创建: useradd -s /sbin/nologin mysql )
(3)拷贝配置文件,启动脚本
cd /usr/local/mysql
cp support-files/my-small.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
编辑 mysqld
basedir=/usr/local/mysql
datadir=/data/mysql
(4)初始化(之后会在 /data/mysql 下有 2 个目录mysql, test)
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
(5)启动主mysql
/etc/init.d/mysqld start
二、安装从mysql
(0)复制mysql解压目录到从上
cp -r /usr/local/mysql /usr/local/mysql_slave
(1)复制主的配置文件
cp /etc/my.cnf /usr/local/mysql_slave/
(2)修改从配置文件:vim my.cnf
port=3307
socket=/tmp/mysql_slave.sock
datadir=/data/mysql_slave
(3)初始化操作
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave
(4)修改从启动脚本
cp /etc/init.d/mysqld /etc/init.d/mysqldslave
修改:
basedir=/usr/local/mysql_slave
datadir=/data/mysql_slave
conf=$basedir/my.cnf (2个地方,#Try to find basedir in /etc/my.cnf 也需要改)
(5)保存启动从
/etc/init.d/mysqldslave start
科普:
登录mysql的2种方式
(1)host+端口
mysql -h127.0.0.1 -P3306 (主)
mysql -h127.0.0.1 -P3307 (从)
(2)socket
mysql -S /tmp/mysql.sock(主)
mysql -S /tmp/mysql_slave.sock(从)
ps:直接mysql是登录主
(ii)主从配置
一、主mysql创建测试数据库并导入数据
(1)登录主,创建测试数据库db1
mysql ——》 create database db1;
(2)导出主mysql库的数据给db1
mysqldump -S /tmp/mysql.sock mysql > 123.sql
mysql -S /tmp/mysql.sock db1 < 123.sql
二、配置主
(1)修改配置文件vim /etc/my.cnf
(a)server-id=1
(b)log-bin=windysai (名字随便)
(c)可选参数(2选1,不选:全部同步) ——》建议不要添加,容易出现问题
binlog-do-db=需要复制的数据库名,多个数据库名,使用逗号分隔。
binlog-ignore-db=不需要复制的数据库库名,多个数据库名,使用逗号分隔。
(2)重启主mysql
/etc/init.d/mysqld restart
(ls /data/mysql 会生成bin_log——2个文件:windysai.000001,windysai.index)
(3)进入主mysql,授权传输bin_log的repl
(a)登录主 mysql
(b)grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456';
说明:如果是不同机器,127.0.0.1 应该为slave端的ip
replication:复制权限的统称
刷新系统权限表(把内存中的数据刷到磁盘中) :flush privileges;
(4)进入主库,进行锁表(只读),防止数据写入
flush tables with read lock;
(5)show master status; (记下File 和 Position的值,配置从服务器用到)
三、配置从
(1)vim /usr/local/mysql_slave/my.cnf (ps:不在/etc/init.d/目录下)
(a)server-id = 111 (和主要不同!)
(b)可选参数 ——》要配合master端一起用
replicate-do-db=db1,db2
replicate-ignore-db=mysql
配置文件最好加多个 read-only 参数(从库只能读,不能修改数据),否则容易造成数据混乱
也可以用set设置:set global read_only = on;
(2)拷贝master上的db1库的数据到slave上(保证主从的库是一样的)
(a)mysql -S /tmp/mysql_slave.sock -e "create database db1"
(b)mysql -S /tmp/mysql_slave.sock db1 <123.sql
说明:
如果是2台服务器做主从
master端:
1、mysqldump -uroot -pyourpasswd db1 > db1.sql
2、scp db1.sql root@slave的ip:/root
(iii)实现同步并测试
一、从mysql设置
(1)登录从
mysql -S /tmp/mysql_slave.sock
(2)从停止复制:slave stop
(3)change master to master_host='127.0.0.1', master_port=3306, master_user='repl', master_password='123123', master_log_file="jiayi.000001', master_log_pos=106;
(4)重启从mysql
/etc/init.d/mysqldslave restart
(5)重新登录从mysql
(6)从开启复制: slave start
(7)查看状态
show slave status\G; (2个yes)
![](https://images2015.cnblogs.com/blog/520218/201611/520218-20161119231604123-1488548063.png)
(iv)测试主从
一、主mysql登录: mysql
(1)主mysql 解锁表的读read lock
unlock tables;
(2)删除 db1 的某些表
use db1; show tables;
drop tables help_category;
二、从登录: mysql -S /tmp/mysql_slave.sock -P3307
(1)查看db1库的tables,发现 没有 help_category
use db1;
show tables;
(2)从把db1库的proc表删除
drop table proc; (主上看没有了proc)
(3)从创建表jiayilinux
show create table `jiayilinux` ....
(参考 show create table user\G;)
(4)删除整个数据库db1
drop database db1;
(v)总结
Mysql的主从机制比较脆弱,需要谨慎操作。
一旦不小心在从上写了数据,那么主从机制也就被破坏了。
另外,
如果重启master端的mysql,务必要把slave停掉,即执行slave stop命令,然后再去重启master的mysql服务,否则很有可能就会中断了。master重启完后,需要把slave开启:slave start