MySQL master-slave environment setup

Server:

192.168.11.131Master

192.168.11.132Slave

Server System

# Cat/etc/redhat-release

CentOS Linux release 7.2.1511 (Core)

1, the following installation process two nodes operate 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 boot up

# Systemctl Enable Mysqld.service

2, two node configuration

Create a Directory

# Mkdir/data/mysql_data

# Chown-r Mysql:mysql/data/mysql_data

Editing a 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 [email protected]: L+7jty6qefut

From the node password

# Cat/var/log/mysqld.log

A temporary password is generated for [email protected]: Slxt;f?671ro

Mysql> set Password=password (' password123! ');

Passwords are set at will (compliance is a rule).

Close 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, from the 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 [email protected] '% ' identified by ' password123! ';

Query OK, 0 rows Affected (0.00 sec)

mysql> grant replication Slave, replication Client on *. * to [email protected] '% ';

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. Synchronization from Node configuration

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 have 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 is given permission by mistake, so the user is deleted

mysql> drop user [email protected] '% ';

7, two node verification

Master 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 (ten) not null,name char (10));

Query OK, 0 rows affected (0.02 sec)

Verification 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

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 master-slave environment setup