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

Mysql master-slave building

Operating system:

[root@localhost ~] # cat / etc/redhat-release

CentOS release 6.8 (Final)

Master (main): 192.168.137.32

Slave (from): 192.168.137.33

Step 1: install the MySQL database under CentOS6.x

Here I use the script to install mysql5.7 directly.

Vim auto_install_mysql.sh

#! / bin/bash

Yum install-y wget

Wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm

Rpm-Uvh mysql57-community-release-el6-9.noarch.rpm

Yum clean all

Sleep 2

Yum install mysql-community-server-y

/ etc/init.d/mysqld start

Chkconfig-level 2345 mysqld on

For i in `grep 'temporary password' / var/log/mysqld.log | awk-F ":"' {print $2}'`

Do

Mysql-uroot-pallei-e "set global validate_password_policy=0;"-b-- connect-expired-password

Mysql-uroot-pallei-e "set global validate_password_length=6;"-b-- connect-expired-password

Mysql-uroot-pallei-e "ALTER USER 'root'@'localhost' IDENTIFIED BY' abc123';"-b-- connect-expired-password

/ usr/bin/mysqladmin-u root-Playi password 'abc123'-b-connect-expired-password

Mysql-uroot-p "abc123"-e "show databases;"

Echo "mysql is install ok"

Done

Service mysqld restart

Iptables-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 3306-j ACCEPT

Service iptables save

/ etc/init.d/iptables restart

# Note: there is a security mechanism in mysql5.7, and the newly installed database has a random password in / var/log/mysql.log

To facilitate the operation, I changed the password abc123.

Step 2: modify the database to be accessible by external ip

Mysql-uroot-p enter the password abc123 to log in to the database

If you want the mysql account myuser to connect to the mysql server from any host with the password mypassword, enter it at the mysql command line:

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' abc123' WITH GRANT OPTION

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# when it is reported that the password currently set does not meet the password policy, the following is the solution

Mysql > set global validate_password_policy=0; # sets the password complexity to 0, which means low

Query OK, 0 rows affected (0.14 sec)

Mysql > set global validate_password_length=6; # set password length to 6

Query OK, 0 rows affected (0.02 sec)

Mysql > grant all privileges on *. * to 'root'@'%' identified by' abc123' with grant option

Query OK, 0 rows affected, 1 warning (0.29 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.41 sec)

Mysql > quit

Bye

Then flush privileges refresh permissions enter quit save exit

Master (192.168.137.32):

Add the following two new lines

Vim / etc/my.cnf

Server-id=1

Log-bin=mysql-bin

Slave (192.168.137.33):

Vim / etc/my.cnf

Server-id=2

Log-bin=mysql-bin

Server-id is unique. Each machine takes its own ID name master: service_id=1 slave:server_id=2

1) next make sure that the server_id on slave and master is correct. You can run SHOW VARIABLES LIKE'server_id'; on the mysql of slave and master respectively to see if the server_id is the same as your configuration

Master:

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.12 sec)

Slave:

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

1 row in set (0.11 sec)

2) restart two mysql service service mysqld restart of master,slaver respectively

3) log in to mysql-u root-p and enter.

4) enter abc123 password

5) master input

6) Mysql > show master status

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000001 | 154 | |

+-+

7) record the values of FILE and Position, which are needed later when operating from the server.

8) configure input within the MYSQL of the slave server

Mysql > change master to

-> master_host='192.168.137.32'

-> master_user='root'

-> master_password='abc123'

-> master_log_file='mysql-bin.000001'

-> master_log_pos=154

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

9) start the Slave synchronization process after correct execution

10) start slave to view slave status

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.137.32

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: localhost-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:

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: 154

Relay_Log_Space: 531

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: d7780cea-92ec-11e7-b97b-000c29e62b50

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)

11) where the red labels Slave_IO_Running and Slave_SQL_Running must be YES to indicate that the state is normal.

Test master-slave replication

1: determine the master first. There are no custom tables on the slave library.

2: operations on the primary server

Create a database on the master master server

Create database mydb2

On the slave server

Show databases;, see if there's any mydb2.

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: No

There's a problem. Slave_SQL_Running should be Yes.

Looking further down, there is a false hint:

Last_Errno: 1053

Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;. Query: 'INSERT INTO hx_stat_record. (a SQL sentence)'

Here are instructions on how to do this:)

First stop slave, then execute the prompt statement, and then SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

Start slave

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If it still doesn't work, first close slave,mysql > stop slave, then reconfigure the offset, from the main library: show master status; and then from the library mysql, enter:

Change master to

Master_host='192.168.137.32'

Master_user='root'

Master_password='abc123'

Master_log_file='mysql-bin.000001'

Master_log_pos=154

Look again from the server whether there is the same database table, some are successful.

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.