CentOs MySQL 数据库主从同步配置解析

无情 阅读:219 2021-03-31 21:49:07 评论:0

前提条件:

硬件要求:

1、主从服务器系统版本和版本位数一致

2、MySQL  版本一致。

服务器配置

master:120.76.112.207

slave:120.25.58.50


CentOS 安装MySQL 具体可以参考:http://blog.csdn.net/zhouzhiwengang/article/details/51701537


Master服务器配置(120.76.112.207)

编辑MySQL 配置信息

#vi  /etc/my.cnf

Welcome to aliyun Elastic Compute Service! 
 
[[email protected] ~]# cat /etc/my.cnf 
# For advice on how to change settings please see 
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html 
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the 
# *** default location during install, and will be replaced if you 
# *** upgrade to a newer version of MySQL. 
 
[mysqld] 
 
# Remove leading # and set to the amount of RAM for the most important data 
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. 
# innodb_buffer_pool_size = 128M 
 
# Remove leading # to turn on a very important data integrity option: logging 
# changes to the binary log between backups. 
log_bin = mysql-bin 
server-id = 1 
user = mysql 
#主从复制配置   
innodb_flush_log_at_trx_commit=1   
sync_binlog=1   
#需要备份的数据库   
binlog-do-db=test   
#不需要备份的数据库   
binlog-ignore-db=mysql  
#不需要备份的数据库 
binlog-ignore-db=information_schema 
#不需要备份的数据库 
binlog-ignore-db=performance_schema 
 
# These are commonly set, remove the # and set as required. 
basedir = /usr/local/mysql 
datadir = /db/mysql/data 
#port =3306 
#server_id = 120.76.112.207 
#socket =  
 
 
# Remove leading # to set options mainly useful for reporting servers. 
# The server defaults are faster for transactions and fast SELECTs. 
# Adjust sizes as needed, experiment to find the optimal values. 
# join_buffer_size = 128M 
# sort_buffer_size = 2M 
# read_rnd_buffer_size = 2M  
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log_error = /var/log/mysql/error.log  
#pid_file = /var/lib/mysql/mysql.pid 
#开启查询缓存 
#explicit_defaults_for_timestamp=true 
symbolic-links=0   
#innodb_force_recovery=1  

提示:binlog-ignore-db 表示不需要备份的数据库 、binlog-do-db表示需要备份数据库,如果两个属性都未配置,那就默认标识同步所有的数据库。


修改配置文件信息(/etc/my.cnf),需要重启mysql 服务,才会生效。

# /etc/init.d/mysql restart 或者 #service mysql restart


为MySQL 创建同步用户

#cd /usr/local/mysql/bin  (mysql 安装目录)

#mysql -u root -p            (mysql 管理员登入 )

Enter PassWord


创建用户:

mysql> create user 'mastj'@'192.168.1.16' identified by '123456';        

mysql> grant replication slave on *.* to 'mastj'@'192.168.1.16' identified by '123456';


查看master 状态:

mysql > show master status;


Slave(120.25.58.50)服务器配置

修改mysql 配置文件信息

#vi /etc/my.cnf

[[email protected] ~]# cat /etc/my.cnf 
# For advice on how to change settings please see 
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html 
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the 
# *** default location during install, and will be replaced if you 
# *** upgrade to a newer version of MySQL. 
 
[mysqld] 
 
# Remove leading # and set to the amount of RAM for the most important data 
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. 
# innodb_buffer_pool_size = 128M 
 
# Remove leading # to turn on a very important data integrity option: logging 
# changes to the binary log between backups. 
# log_bin 
user=mysql 
#datadir = /db/mysql/data 
 
# These are commonly set, remove the # and set as required. 
 basedir = /usr/local/mysql 
 datadir = /db/mysql/data 
# port = ..... 
# server_id = ..... 
#socket =/usr/local/mysql/mysql.sock 
 
# Remove leading # to set options mainly useful for reporting servers. 
# The server defaults are faster for transactions and fast SELECTs. 
# Adjust sizes as needed, experiment to find the optimal values. 
# join_buffer_size = 128M 
# sort_buffer_size = 2M 
# read_rnd_buffer_size = 2M  
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
log_error = /var/log/mysql/error.log 
log_bin = mysql-bin 
server-id = 2 
重新启动MySQL服务

# /etc/init.d/mysql restart 或者service mysql restart

执行

 mysql> change master to master_host='192.168.1.18',

              master_user='mastj',

              master_password='123456',

              master_port=3306,

              master_log_file='mysql-bin.000003',

              master_log_pos=2005,

              master_connect_retry=10;


参数详解:

master_host:主服务器的IP。

master_user:配置主服务器时建立的用户名

master_password:用户密码

master_port:主服务器mysql端口,如果未曾修改,默认即可

master_log_file:日志文件名称,填写查看master状态时显示的File

master_log_pos:日志位置,填写查看master状态时显示的Positio

master_connect_retry:重连次数


启动数据同步线程

mysql >start slave;


简称数据同步状态:

mysql> show slave status\G 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.1.18 
                  Master_User: mastj 
                  Master_Port: 3306 
                Connect_Retry: 10 
              Master_Log_File: mysql-bin.000003 
          Read_Master_Log_Pos: 2369 
               Relay_Log_File: jhq0113-relay-bin.000002 
                Relay_Log_Pos: 647 
        Relay_Master_Log_File: mysql-bin.000003 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes

若Slave_IO_Running和Slave_SQL_Running均为Yes,则表示连接正常。


       此时就可以测试主从复制了





声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
KIKK导航

KIKK导航

关注我们