首页 > 代码库 > mysql主从复制
mysql主从复制
mysql主从复制
实验环境
主：192.168.110.33
从：192.168.110.59
iptables and selinux disabled
主：服务器端
1.安装并初始化
# yum install -y mysql -mysql-server
#/etc/init.d/mysql start
# mysql_secure_installation #设置root密码
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
2.用户、修改/etc/my.cnf文件
# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
57 server-id = 1 #id唯一
59 binlog-do-db = test #指定同步test数据库
60 binlog-ignore-db = mysql #避免同步 mysql 数据库,以免不必要的麻烦
3.创建同步账户并授权
# mysql -p密码
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
mysql> create table mysql( username varchar(25) not null, password varchar(25) not null);
mysql> desc mysql;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO | | NULL | |
| password | varchar(25) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
mysql> insert into mysql value(‘girl‘,123)；
mysql> insert into mysql value(‘boy‘,123) ;
mysql> select * from mysql;
+----------+----------+
| username | password |
+----------+----------+
| girl | 123 |
| boy | 123 |
+----------+----------+
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO cyl@‘192.168.110.%‘ IDENTIFIED BY ‘cyl&647308‘;
mysql> flush privileges;
mysql> quit
Bye
3.备份test数据库并发送二进制日志到从
[root@cyl_test1 mysql]# pwd
/var/lib/mysql
# file mysql-bin.000003
mysql-bin.000003: MySQL replication log
# mysqldump -p‘cyl&647308‘ test > test.sql或
#mysqldump -p‘数据库密码‘ test > test.sql
# scp test.sql mysql-bin.000003 192.168.110.59:
从:客户端
1.安装、初始化、修改/etc/my.cnf文件
# yum install -y mysql -mysql-server
#/etc/init.d/mysql start
# mysql_secure_installation #设置root密码
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
# vim /etc/my.cnf
server-id = 2 #id唯一
2.用户、登录同步账户（用户必须与服务端保持一致）
# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
# mysql -ucyl -p‘cyl&647308‘ -h 192.168.110.33
mysql> show databases; #若没有test数据库手动创建
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
mysql> quit
Bye
3.将数据库导入，登录数据库看到数据同步成功（在服务端发送二进制文件的位置）
# mysql -p‘cyl&647308‘ test<test.sql 或
#mysql -p‘数据库密码‘ test < test.sql
# mysql -p密码
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mysql |
+----------------+
mysql> select * from mysql;
+----------+----------+
| username | password |
+----------+----------+
| girl | 123 |
| boy | 123 |
+----------+----------+
主：服务端
在服务端查看master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1834 | test | mysql |
+------------------+----------+--------------+------------------+
从：客户端
在客户端设置允许服务端同步数据
# mysql -p
mysql> stop slave;
mysql> change master to master_host=‘192.168.110.33‘, master_user=‘cyl‘,master_password=‘cyl&647308‘, master_log_file=‘mysql-bin.000003‘,master_log_pos=1834; #此用户为master端授权的同步用户
mysql> start slave;
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.33
Master_User: cyl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1834
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主：服务端
改动数据库数据观察从是否同步
# mysql -p密码
mysql> use test;
mysql> insert into mysql value (‘love‘,123);
mysql> select * from mysql;
+----------+----------+
| username | password |
+----------+----------+
| girl | 123 |
| boy | 123 |
| love | 123 |
+----------+----------+
从：客户端
# mysql -p密码
mysql> use test;
mysql> select * from mysql;
+----------+----------+
| username | password |
+----------+----------+
| girl | 123 |
| boy | 123 |
| love | 123 |
+----------+----------+
主：服务端
mysql> delete from mysql where username=‘love‘;
从：客户端
mysql> select * from mysql;
+----------+----------+
| username | password |
+----------+----------+
| girl | 123 |
| boy | 123 |
+----------+----------+
本文出自 “真水无香” 博客，请务必保留此出处http://chengyanli.blog.51cto.com/11399167/1846794
mysql主从复制