前期准备两台服务器:
注意要点:
1、两台服务器的 mysql 版本尽可能的保持一致性(出错概率会小很多),此处使用 yum update 至最新版 mysql Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using EditLine wrapper
2、my.cnf 配置文件的 server-id 千万保证它的唯一性
主库信息:
服务器:
IP地址:115.28.222.29
内网IP:10.161.33.18
用户名:
密码:
数据库:
数据库地址:115.28.222.29
数据库名称:
数据库用户:
数据库密码:
数据库端口:
从库服务器:
IP地址:120.27.30.132
内网IP:10.163.101.235
用户名:
密码:
数据库:
数据库地址:120.27.30.132
数据库名称:auction
数据库用户:auction
数据库密码:e2e879558e90b449
数据库端口:3306
创建 Mysql 主从数据同步:
登录主服务器:
1、如果已经存在数据,优先做数据库的导出备份;以及数据的同步。
2、参照 my.cnf 文件修改内容
3、重启 mysql,服务 service mysqld restart
4、连接 mysql, mysql -u root -p
5、CREATE USER ‘user_name’@’10.163.101.235’ IDENTIFIED BY ‘7A6aqcdx’
6、GRANT REPLICATION SLAVE ON *.* TO ‘user_name’@’10.163.101.235’ IDENTIFIED BY ‘7A6aqcdx’
7、SHOW MASTER STATUS;
记录日志文件(mysql-bin.000003 120):
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
登录从库服务器:
1、参照 my.cnf 修改配置项
2、重启 mysql。 service mysqld restart;
3、登录 mysql。 mysql -u root -p ;
4、 CHANGE MASTER TO
MASTER_HOST=’10.161.33.18’,
MASTER_USER=’user_name’,
MASTER_PASSWORD=‘7A6aqcdx’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000003’, //主库查询得到的文件名
MASTER_LOG_POS=120, //主库查询得到的文件位置
MASTER_CONNECT_RETRY=15;
5、启动 slave 进程。 START SLAVE;
6、检查主从同步。 show slave status\G;
其中: Slave_IO_Running 与 Slave_SQL_Running 的值都必须为 YES,才能表明状态正常。
主库服务器的 my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
character_set_server=utf8
server-id=1
#
# 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
log_bin=/var/log/mysql-bin
log-bin_index=/var/log/mysql-bin.index
max_binlog_size=128M
expire_logs_days=7
sync_binlog=1
binlog_cache_size=1M
binlog-format=MIXED
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
从库服务器的 my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
server-id=224
#
# 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 = 8M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid