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 |

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

看了以上关于mysql主从复制实验环境测试，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。