不使用中间键的主从配置
By skyshappiness Posted 2017-01-03 21:51:22 In

前期准备两台服务器:

注意要点:

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

友情链接
联系方式
  • 邮箱 / E-mail:skyshappiness@gmail.com