MYSQL主从环境搭建

服务器：

创新互联建站是一家集网站建设,金塔企业网站建设,金塔品牌网站建设,网站定制,金塔网站建设报价,网络营销,网络优化,金塔网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

192.168.11.131 master

192.168.11.132 slave

服务器系统

# cat /etc/redhat-release

CentOS Linux release 7.2.1511 (Core)

1、下面安装过程两个节点操作相同

# rpm -qa | grep mariadb

postfix-2.10.1-6.el7.x86_64

# rpm -qa | grep mariadb

mariadb-libs-5.5.44-2.el7.centos.x86_64

# rpm -ev postfix-2.10.1-6.el7.x86_64

# rpm -ev mariadb-libs-5.5.44-2.el7.centos.x86_64

# rpm -ivh MySQL-community-common-5.7.18-1.el7.x86_64.rpm

# rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm

# rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm

# rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm

设置开机启动

# systemctl enable mysqld.service

2、两节点配置

创建目录

# mkdir /data/mysql_data

# chown -R mysql:mysql /data/mysql_data

编辑配置文件

# vi /etc/my.cnf

datadir=/data/mysql_data

character_set_server=utf8

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# innodb optimization

innodb_buffer_pool_size=8G

innodb_log_file_size=256M

innodb_flush_method=O_DIRECT

max_connections=500

innodb_autoextend_increment=128

启动服务

# service mysqld start

主节点密码

# cat /var/log/mysqld.log

A temporary password is generated for root@localhost: l+7jtY6QEfut

从节点密码

# cat /var/log/mysqld.log

A temporary password is generated for root@localhost: sLxt;f?671RO

mysql> set password=password('Password123!');

密码随意设置（符合规则就行）

关闭服务

# service mysqld stop

3、主节点配置

# vi /etc/my.cnf

server-id=1

log-bin=mysql-bin

binlog_format=mixed

innodb_flush_log_at_trx_commit=1

sync_binlog=1

expire_logs_days=15

relay_log=mysql-realy-bin

4、从节点配置

# vi /etc/my.cnf

server-id=2

log_bin=mysql-bin

relay_log=mysql-relay-bin

log-slave-updates=on

expire_logs_days=15

replicate-ignore-db=sys

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

启动服务

# service mysqld start

5、主节点配置同步

mysql> create user repluser@'%' identified by 'Password123!';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave, replication client on *.* to repluser@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 2165 | | | |

+------------------+----------+--------------+------------------+-------------------+

6、从节点配置同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.131', MASTER_USER='repluser', MASTER_PASSWORD='Password123!', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=2165;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.131

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 2165

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2165

Relay_Log_Space: 527

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: ce43b0d9-7f3e-11e8-abc5-063f580099bf

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

误给用户赋予了权限，故删除用户

mysql> drop user missingcust@'%';

7、两节点验证

主节点配置验证：

mysql> create database ceshi_db;

Query OK, 1 row affected (0.00 sec)

mysql> use ceshi_db;

Database changed

mysql> create table home(id int(10) not null,name char(10));

Query OK, 0 rows affected (0.02 sec)

从节点验证;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ceshi_db |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> use ceshi_db;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------------+

| Tables_in_ceshi_db |

+--------------------+

| home |

+--------------------+

1 row in set (0.00 sec)

文章标题：MYSQL主从环境搭建
当前URL：http://scyanting.com/article/jhohdh.html

MYSQL主从环境搭建

其他资讯