mysql主从库联查_mysql6.5主从库同步

mysql主从同步

1、192.168.56.20(主)和192.168.56.21(从)安装MYSQL6.5数据库

wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

rpm -ivh mysql-community-release-el6-5.noarch.rpm

yum repolist all | grep mysql

yum install mysql-community-server -y

2、启动

service mysqld start

3、创建用户具有同步权限，192.168.56.21是从库的ip

创建数据库

mysql> create database test1;

mysql> use test1;

mysql> set names utf8;

grant replication slave on . to 'repl'@'192.168.56.21' identified by 'repl';

flush privileges;

3、修改主节点配置文件

vim /etc/my.cnf

【mysql】

server-id=101 #每个节点必须不一样

log-bin=/var/lib/mysql/mysql-bin

4、重新启动主库的msyqld服务

service mysqld restart

5、登录数据库设置数据库读锁

mysql>flush tables with read lock;

6、查看binlog日志文件名和偏移量(mysql-bin.000001和120)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 120 | | | |

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

1 row in set (0.00 sec)

7、备份数据库到当前目录

mysqldump test1 > test1.sql

8、复制sql文件到从库

scp test1.sql 192.168.56.21:/var/lib/mysql/

9、解锁

mysql> unlock tables;

10、从库导入sql文件到test1

mysql> create database test1;

mysql> use test1;

mysql> set names utf8;

mysql> source /var/lib/mysql/test1.sql;

11、编辑从库配置文件

vim /etc/my.cnf

server-id=109

12、重启从库服务

service mysqld restart

13、配置从库

关闭复制功能

mysql> stop slave;

mysql>change master to master_host='192.168.56.20',master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=120;

启动复制

Mysql>start slave;

检查复制功能

mysql>show slave status\G

两个进程需要yes才正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

14、在主库新建数据库，然后去从库看是否同步，同步表示正常。

create database hi_db;