Onex ecstore多级部署之配置mysql主从

代码 2016-08-15 小酱沫手机阅读

ecstore多级部署之配置mysql主从（多台数据库进行相关配置详解）

1、配置主服务器：

vim /etc/my.cnf

[client]

port = 3306 socket = /tmp/mysql.sock

[mysqld]

port = 3306 socket = /tmp/mysql.sock #datadir =/data/mysql //basedir=/data/mysql skip-external-locking //datadir=/data/mysql/data key_buffer_size = 256M max_allowed_packet = 128M table_open_cache = 128 sort_buffer_size = 16M net_buffer_length = 32K read_buffer_size = 128M read_rnd_buffer_size = 256M myisam_sort_buffer_size = 256M expire_logs_days = 1 max_connections=3000 max_user_connections=3000 skip-name-resolve binlog-do-db=ecstore binlog-ignore-db=mysql binlog-ignore-db=test log-bin=mysql-bin binlog_format=mixed server-id = 1

[mysqldump]

quick max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

2、创建数据库ecstore

CREATE DATABASE ecstore;
导入信息
GRANT REPLICATION SLAVE ON *.* TO 'root'@'slaveIP' IDENTIFIED BY 'yinduo123'
查看状态
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1033 | ecstore      | mysql,test       |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

表示配置成功
3、设置从服务器：vim /etc/my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]

[mysqldump]

quick max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

进入数据库，先关闭slave

STOP SLAVE;
导入信息
CHANGE MASTER TO MASTER_HOST='主服务IP', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000115', MASTER_LOG_POS=1033 

# mysql-bin.000002是在主服务器mysql中通过  show master status;  查询到的 字段file的值
# 1033是对应 Position的值

开启SLAVE
START SLAVE;
查看同步情况
SHOW SLAVE STATUS\G;

4.最后修改ecstore相关配置

vim config/config.php

// 数据库配置

define('DB_USER', 'ecstore'); // 数据库用户名

define('DB_PASSWORD', 'ecstore123'); // 数据库密码

define('DB_NAME', 'ecstore'); // 数据库名

define('DB_HOST', '127.0.0.1'); // 数据库HOST

define('DB_PREFIX', 'sdb_');

# define('DB_PCONNECT', 1); // 是否启用数据库持续连接？

// 数据库集群.

define('DB_SLAVE_NAME', DB_NAME);

define('DB_SLAVE_USER', DB_USER);

define('DB_SLAVE_PASSWORD', DB_PASSWORD);

define('DB_SLAVE_HOST', DB_HOST);

可参考下图进行配置：

ps：这里的host为主sql的内网IP