Construction of MYSQL master-slave environment

Server:

192.168.11.131 master

192.168.11.132 slave

Server system

# cat / etc/redhat-release

CentOS Linux release 7.2.1511 (Core)

1. The operation of the two nodes in the following installation process is the same.

# 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

Set up boot boot

# systemctl enable mysqld.service

2. Configuration of two nodes

Create a directory

# mkdir / data/mysql_data

# chown-R mysql:mysql / data/mysql_data

Edit configuration file

# 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

Start the service

# service mysqld start

Master node password

# cat / var/log/mysqld.log

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

Slave node password

# cat / var/log/mysqld.log

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

Mysql > set password=password ('password 123 requests')

The password is set at will (as long as it complies with the rules)

Shut down the service

# service mysqld stop

3. Master node configuration

# 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. Slave node configuration

# 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

Start the service

# service mysqld start

5. Master node configuration synchronization

Mysql > create user repluser@'%' identified by'Password 123customers'

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

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 2165 | |

+-+

6. Configure synchronization from the node

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.131', MASTER_USER='repluser', MASTERPASSWORD password password 123 password, 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)

The user was mistakenly given permissions, so the user was deleted

Mysql > drop user missingcust@'%'

7. Two-node verification

Primary node configuration verification:

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)

Verify from the node

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)