CentOs MySQL 数据库主从同步配置

前端之家收集整理的这篇文章主要介绍了CentOs MySQL 数据库主从同步配置前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

前提条件:

硬件要求:

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

2、MysqL 版本一致。

服务器配置

master:120.76.112.207

slave:120.25.58.50


CentOS 安装MysqL 具体可以参考:http://www.jb51.cc/article/p-gfbzfsmj-ty.html


Master服务器配置(120.76.112.207)

编辑MysqL 配置信息

#vi /etc/my.cnf

Welcome to aliyun Elastic Compute Service!

[root@iZ94phz01rnZ ~]# 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 @R_502_103@.
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

[root@iZ94qvmp90hZ ~]# 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,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 @R_502_103@.
 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,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',43); font-family:Arial; font-size:14px; line-height:26px"> master_password='123456',43); font-family:Arial; font-size:14px; line-height:26px"> master_port=3306,43); font-family:Arial; font-size:14px; line-height:26px"> master_log_file='MysqL-bin.000003',43); font-family:Arial; font-size:14px; line-height:26px"> master_log_pos=2005,43); font-family:Arial; font-size:14px; line-height:26px"> 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,则表示连接正常。

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

猜你在找的CentOS相关文章