Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Construction of MYSQL master-slave environment

2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report