安装包：官网下载安装程序
http://downloads.mysql.com/archives/community/
mysql-community-client-5.7.9-1.el6.x86_64.rpm
mysql-community-common-5.7.9-1.el6.x86_64.rpm
mysql-community-libs-5.7.9-1.el6.x86_64 (1).rpm
mysql-community-server-5.7.9-1.el6.x86_64.rpm
yum本地安装
yum localinstall mysql-community*
初始化数据库，修改密码
mysqld_safe --skip-grant-tables
update mysql.user set authentication_string=password('mysql') where user="root";
flush privileges;
alter user 'root'@'localhost' identified by 'root';
增加一个用于同步数据的账户并设置相关的权限
set global validate_password_policy=0; --设置密码策略
grant replication slave on *.* to 'rsync'@'192.168.%.%' identified by 'rsync@2016'; ##增加同步账号，并附加权限
修改mysql配置文件my.cnf
/etc/my.cnf
master上my.cnf 增加以下几行
server-id=1
log-bin=master-bin ##主服务器二进制日志文件前缀名
log-bin-index=master-bin.index ##索引文件
slave上my.cnf增加以下几行
server-id=2 ## server-id必须唯一
relay-log-index=slave-relay-bin.index ##索引文件
relay-log=slave-relay-bin ##备服务器二进制日志文件前缀名
在slave开启复制功能
查看master状态
show master status；
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在从服务器上开启复制功能
change master to master_host='192.168.32.11',master_user='rsync',master_password='rsync@2016',master_log_file='master-bin.000001',master_log_pos=154;
查看salve状态,并启动slave
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.11
Master_User: rsync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.32.11
Master_User: rsync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c1095776-e1ec-11e5-889b-000c29af54e2
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
1 row in set (0.00 sec)
ERROR:
No query specified
主从复制已经搭建完成，接下来我们测试
master上创建数据库xmj
mysql> create database xmj;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xmj |
+--------------------+
5 rows in set (0.06 sec)
创建数据表测试
mysql> use xmj;
Database changed
mysql> create table xmj(id int(3),name char(10));
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+---------------+
| Tables_in_xmj |
+---------------+
| xmj |
+---------------+
1 row in set (0.00 sec)
在表xmj中插入一条记录
mysql> insert into xmj values (001,'yixiuge');
Query OK, 1 row affected (0.07 sec)
mysql> select * from xmj;
+------+---------+
| id | name |
+------+---------+
| 1 | yixiuge |
+------+---------+
1 row in set (0.00 sec)
查看salve数据库和表是否同步
mysql> use xmj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_xmj |
+---------------+
| xmj |
+---------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_xmj |
+---------------+
| xmj |
+---------------+
1 row in set (0.00 sec)
mysql> select * from xmj;
+------+---------+
| id | name |
+------+---------+
| 1 | yixiuge |
+------+---------+
1 row in set (0.00 sec)
测试数据库和表都正常同步，此刻主从复制架构已经搭建完成
但是目前我们salve数据库还具有可写入操作，这样容易造成主从同步的时候数据库错乱和损坏，
此时就需要设置，slave为只读模式
查看数据库的运行状态
mysql> show global variables like 'read%';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.15 sec)
此时read_only = off 只读模式处于关闭状态，我们打开
mysql> set global read_only=1;
Query OK, 0 rows affected (0.07 sec)
mysql> show global variables like 'read%';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_only | ON |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)
#这里是直接修改的，但是重启后会还原，还需要加入配置文件中
vi /etc/my.cnf
添加一行
read-only = on
到这里主从复制已经完全搭建完成
注：这里是一个简单的主从架构，考虑业务和数据的安全的方方面面，还需要根据自己的需求设置相应的参数。
一休哥Configure MySQL Master-Slave replication
原创
©著作权归作者所有：来自51CTO博客作者一一休哥的原创作品，请联系作者获取转载授权，否则将追究法律责任
下一篇：我的友情链接
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
重置mysql主从同步（MySQL Reset Master-Slave Replication）数据库 运维 shell 服务器 重置
-
MySQL 8.0主从（Master-Slave）配置
MySQL 8.0主从复制原理
MySQL 8.0 主从 复制原理
