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

/ 0评 / 1

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]
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       = 2

[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

1
2

ps:如果需要深度优化,可参考文章:https://jinjiajin.net/107.html

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注