首页 > 代码库 > mysql主从复制+读写分离 菜鸟入门

mysql主从复制+读写分离 菜鸟入门

MYsql主从复制

1.mysql主从复制原理:

Master将数据变化记录到二进制日志中【binary log】

Slave将master的二进制日志【binary log】拷贝到自己的中继日志relay log中。

Slave将中继入日志【relay log】事件在做一次,将数据变化,反应到自身数据库。

简述:mysql主从复制其实就是完全备份,和二进制日志备份还原的过程。二进制日志的还原基本上是实时进行的。注意不是完全实时,而是异步实时,主从直接的执行有延迟。如果master压力过大,则延迟就会很长,

原理详解:一个是i/o线程,负责:读取主服务器的二级制日志文件binary log。并将其保存在自己的中继日志中。

               -个是sql线程,负责:复制执行中继日志【relay log】。

技术分享

1.安装mysql服务器:二进制包安装

master:192.168.1.105

slave :192.168.1.106

安装:

查看系统配置:uname -a

添加用户:useradd -s /sbin/nologin -M mysql

下载mysql二进制安装包

wget:  http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-x86_64-glibc23.tar

解压移动到/usr/local/src

tar -xvzf mysql-5.1.73-linux-x86_64-glibc23.tar

mv mysql-5.1.73-linux-x86_64-glibc23 /usr/local/mysql

创建数据存放位置:mkdir -p /data/mysql

设定权限:chown -R mysql:mysql /data/mysql

初始化:进入/usr/local/mysql下。注意:定义数据存放位置及数据库所属主

# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

拷贝mysql配置文件到/etc/下

cp support-files/my-large.cnf /etc/my.cnf

拷贝启动脚本并修改其属性

cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

添加path路径便于管理:

vim /etc/profile.d/path.sh

export PATH=$PATH:/usr/local/mysql/bin

运行:source /etc/profile.d/path.sh

或者:echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile

service mysqld start

 

--------------------------------基础安装到此--------------------------------

 

mysql主从复制配置正式开始

配置mysql master主:

修改主配置文件:打开log_bin功能,设置服务器id默认企业中此值可以不用修改

默认为1.即可。

server-id = 1 服务器id 每一个服务器id必须唯一。

log-bin=wangyunlong可以自定义,默认为:mysql-bin可以忽略不变。

Binlog二进制日志:主服务变更信息会记录再次文件中:/data/mysql下

退出登录更改root密码:

指定需要同步的库:

binlog-do-db=db1,(指定需要同步的库)

binlog-ignore-db=db1,(指定出除此库外其它全部同步。此时binlog-do-db和binlog-ignore-db二选一)

保存退出。

设置主数据库root密码

mysql>update user set password=password(‘wangyunlong‘) where user=‘root‘;
mysql>flush privileges;

配置主从测试文件

在住上创建测试数据库db1

mysql -uroot -pwangyunlong1992 -e "create database db1"

导出主的mysql数据库然后倒入给db1数据库。

mysqldump -uroot -p"wangyunlong1992" mysql > 123.sql

mysql -uroot -p"wangyunlong1992" db1 < 123.sql

查看master id

mysql> show variables like ‘server_id‘;

查看binlog是否开启

show variables like ‘log_bin‘;

mysql> show variables like ‘log_bin‘;

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

| Variable_name | Value |

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

| log_bin          | ON      |

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

1 row in set (0.00 sec)

 

master上建立主从复制的账号

masger上建立复制账号“rep”密码“123456”用于从服务器i/o读取主binlog数据所需的账号密码。

mysql> grant replication slave on *.* to ‘rep‘@‘192.168.1.%‘ identified by ‘123456‘;

检查主库创建的rep复制账号:

mysql> select user,host from mysql.user;

mysql> select user,host from mysql.user;

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

| user | host                    |

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

| root | 127.0.0.1            |

| rep  | 192.168.1.%       |

|        | bogon                 |

| root | bogon                 |

|        | localhost             |

| root | localhost             |

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

6 rows in set (0.00 sec)

 

检查权限

mysql> show grants for rep@‘192.168.1.%‘;

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

| Grants for rep@192.168.1.%                                                                                                                                                                      |

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

| GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |

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

1 row in set (0.00 sec)

 

对主服务器锁表(考虑备份测试数据完整性。)

mysql> flush tables with read lock;

查看master状态

mysql> show master status;

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

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| wangyunlong.000004 |      106 |     db1      |                  |

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

1 row in set (0.00 sec)

wangyunlong.000002->主数据变化就记录再次文件中。Position 为106

Binlog_do_db:只同步的数据有db1 binlog_ignore_db(除此之外的数据库全部同步;

主配置完成。

 

配置从slave:192.168.1.106

设置从数据密码:wangyunlong

设置从slave id

server-id = 2 服务器id 每一个服务器id必须唯一。

关闭从的binlog功能,关闭只需要注释掉即可。

#log-bin=mysql-bin 
#binlog_format=mixed

保存退出。

查看状态:

mysql> show variables like  ‘log_bin‘;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    3

Current database: *** NONE ***

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

| Variable_name | Value  |

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

| log_bin       | OFF         |

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

1 row in set (0.00 sec)

备份主的db1并拷贝从并倒入

主备份:mysqldump -uroot -pwangyunlong1992 db1>db1.sql

拷贝:scp db1.sql 192.168.1.106:/tmp/

从创建db1库:mysql -uroot -pwangyunlong -e "create database db1"

导入到从mysql -uroot -pwangyunlong db1 < db1.sql

查看从:

mysql> show databases;

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

| Database            |

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

| information_schema  |

| db1                          |

| mysql                       |

| test                          |

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

执行:slave stop

配置从连接主数据库信息

mysql> change master to master_host=‘192.168.1.105‘, master_port=3306, master_user=‘rep‘, master_password=‘123456‘,  master_log_file=‘wangyunlong.000005‘, master_log_pos=106;

解释:

master_host=‘192.168.1.105‘, 主库ip

master_port=3306, 主库端口

master_user=‘rep‘, 主库上建立的复制用户名

master_password=‘123456‘, 复制用户密码

master_log_file=‘wangyunlong.000005‘, 二进制日志文件名称刚才查看状态“show master status”显示的File 信息。

master_log_pos=106;

二进制日志偏移量:查看状态“show master status”显示的Position信息。

从执行slave start;开启从复制开关

开启主:

mysql -uroot -pwangyunlong -e "unlock tables";

查看从是否配置是否成功:

show slave status\G;

显示:如下信息表示成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

完整显示:mysql> show slave status\G;

解释:

Slave_IO_Running: Yes 负责从从库到主库第七binLog日志,并写入从库的中继日志中

Slave_SQL_Running: Yes 负责读取从库的中继日志信息并转化为sql语句引用执行到从数据库中。

Seconds_Behind_Master: 0 复制过程中从库到主库延迟的秒数。企业例判断延迟的方法为:在主库写的时间戳,然后从库中读取时间戳,和当前数据时间进行比较,判断是否延迟。

测试主从配置是否成功:

在主master上进入删除主库db1中的event表。

use db1

drop table event;

在从上看没有了

select count(*)from event;

...继续测试...增加删除插入等.......ok即可。

                                                                            mysql读写分离后续补充

mysql主从复制+读写分离 菜鸟入门