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

MySQL master-slave (MySQL replication) is mainly used for real-time backup or read-write separation of MySQL. Do some preparatory work before configuration, configure two MySQL servers, if your machine can not run two Linux virtual machines at the same time, then consider running two MySQL services on the same machine.

The principle of MySQL master and slave is very simple, to sum up:

(1) only one master can be set for each master and slave.

(2) the master records the binary log file (bin-log) after executing the SQL

(3) connect to the master and get the master's bin-log, store it in the local relay-log, and execute the SQL from the location where it was last executed, and stop synchronization if an error is encountered.

Mysql master-slave configuration replication, also known as Amine B replication, to ensure master-slave data synchronization

A-- > change data-- > bin_log-transfer-- > B-- > repl_log-- > change data

Judging from these replication principles, there can be these inferences:

The main results are as follows: (1) the database between master and slave is not synchronized in real time, and even if the network connection is normal, there is an instant, and the master-slave data is inconsistent.

(2) if the master-slave network is disconnected, the slave will synchronize in batches after the network is normal.

(3) if the slave modifies the data, it is likely to stop synchronization from an error in the execution of the master's bin-log, which is a very dangerous operation. So in general, be very careful to modify the data from above.

(4) A derivative configuration is a two-master configuration, that is, a mutual master-slave configuration, as long as the modifications of the two sides do not conflict, it can work well.

(5) if multi-master is needed, the ring configuration can be used so that the changes of any one node can be synchronized to all nodes.

(6) it can be used in the scenario of read-write separation to reduce the Imax O of a single MySQL.

(7) it can be one master and multiple followers, or it can be mutual master and follower (master)

Main MySQL (master): 192.168.134.128

From MySQL (slave): 192.168.134.129

1. Preparatory work:

(1) modify the hostnames of the two hosts:

Master: 192.168.134.128

[root@master ~] # hostname master

[root@master ~] # vim / etc/sysconfig/network

Hostname=master

From: 192.168.134.129

[root@slave~] # hostname slave

[root@slave~] # vim / etc/sysconfig/network

Hostname=slave

(2) Edit the hosts file on two machines:

Vim / etc/hosts

All add two lines:

192.168.134.128 master

192.168.134.129 slave

(3) turn off the firewalls of the two machines:

Turn off SELinux:

Setenforce 0

Vim / etc/selinux/config

SELINUX=disabled

Turn off iptables:

Iptables-F

Iptables-save

Chkconfig iptables off

two。 Install MySQL on both master and slave:

Master: 192.168.134.128

Enter the source package directory:

[root@master ~] # cd / usr/local/src

Download the MySQL installation package:

[root@master src] # ls

Mysql-5.1.73-linux-x86_64-glibc23.tar.gz

Extract the MySQL package:

[root@master src] # tar zxvf mysql-5.1.73-linux-x86_64-glibc23.tar.gz

Move and rename the installation directory:

[root@master src] # mv mysql-5.1.73-linux-x86_64-glibc23 / usr/local/mysql

View the contents of the installation directory:

[root@master src] # ls / usr/local/mysql/

Bin data include lib mysql-test scripts sql-bench

COPYING docs INSTALL-BINARY man README share support-files

Create mysql users and do not allow them to log in:

[root@master src] # useradd-s / sbin/nologin mysql

Enter the installation directory:

[root@master src] # cd / usr/local/mysql/

Copy the configuration file to the / etc directory to overwrite the original my.cnf:

[root@master mysql] # cp support-files/my-small.cnf / etc/my.cnf

Cp: overwrite "/ etc/my.cnf"? Y

Copy the startup script to the / etc/init.d/ directory and rename it to mysqld:

[root@master mysql] # cp support-files/mysql.server / etc/init.d/mysqld

Edit the startup script:

[root@master mysql] # vim / etc/init.d/mysqld

Define basedir and datadir:

Basedir=/usr/local/mysql

Datadir=/data/mysql

Create the database storage path:

[root@master mysql] # mkdir / data/mysql

Configuration:

[root@master mysql] # / scripts/mysql_install_db-- user=mysql-- datadir=/data/mysql

WARNING: The host 'master' could not be looked up with resolveip.

This probably means that your libc libraries are not 100% compatible

With this binary MySQL version. The MySQL daemon, mysqld, should work

Normally with the exception that host name resolving will not work.

This means that you should use IP addresses instead of hostnames

When specifying MySQL privileges!

Installing MySQL system tables...

170312 23:59:44 [Warning]'--skip-locking' is deprecated and will be removed in a future release. Please use'--skip-external-locking' instead.

OK

Filling help tables...

170312 23:59:44 [Warning]'--skip-locking' is deprecated and will be removed in a future release. Please use'--skip-external-locking' instead.

OK

The presence of two OK indicates that the configuration is successful.

Start MySQL:

[root@master mysql] # / etc/init.d/mysqld start

Starting MySQL. SUCCESS!

View the process:

[root@master mysql] # ps aux | grep mysql

Root 1369 0.2 0.0 106060 1484 pts/0 S 01:00 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql-- pid-file=/data/mysql/master.pid

Mysql 1481 1.5 0.5 265280 21612 pts/0 Sl 01:00 0:00 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql-user=mysql-log-error=/data/mysql/master.err-pid-file=/data/mysql/master.pid-socket=/tmp/mysql.sock-port=3306

Root 1494 0.0 103248 872 pts/0 S + 01:00 0:00 grep mysql

View the port:

[root@master mysql] # netstat-lnp | grep mysql

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 1481/mysqld

Unix 2 [ACC] STREAM LISTENING 18672 1481/mysqld / tmp/mysql.sock

Slave: 192.168.134.129 (the installation and configuration process of the master-slave MySQL is exactly the same, so I won't repeat it here)

There are three ways to log in to MySQL:

1. Log in using an absolute path:

/ usr/local/mysql/bin/mysql

two。 Log in using socket:

Mysql-S / tmp/mysql.sock

3. Log in using host+port:

Mysql-h227.0.0.1-P3306

There is no password by default, and you can use mysqladmin to set the password.

3. Start building master / slave MySQL:

Master: 192.168.134.128

Add MySQL to the environment variable:

[root@master mysql] # vim / etc/profile.d/mypath.sh

Export PATH=$PATH:/usr/local/mysql/bin/

[root@master mysql] # source / etc/profile.d/mypath.sh

Log in to MySQL to create the database db1:

[root@master mysql] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.1.73 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > create database db1

Query OK, 1 row affected (0.00 sec)

Exit mysql first:

Mysql > quit

Copy the mysql library to the db1 library:

Backup to 123.sql:

[root@master mysql] # mysqldump-S / tmp/mysql.sock mysql > 123.sql

Warning: Skipping the data of table mysql.event. Specify the-- events option explicitly.

Restore to db1:

[root@master mysql] # mysql-S / tmp/mysql.sock db1

< 123.sql 再次登录MySQL，查看db1中的内容： [root@master mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.73 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Use db1

Database changed

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

23 rows in set (0.00 sec)

Mysql > quit

Bye

Indicates that the db1 database was created successfully.

Compile the configuration file:

[root@master mysql] # vim / etc/my.cnf

Open the comment in front of log-bin:

Log-bin=mysql-bin

And add a line below it (indicating that only db1 is the master and slave):

Binlog-do-db=db1

(multiple data can be separated by commas: binlog-do-db=db1,db2,db3, or blacklisted: binlog-ignore-db=db1)

Restart MySQL:

[root@master mysql] # / etc/init.d/mysqld restart

Shutting down MySQL... SUCCESS!

Starting MySQL. SUCCESS!

View the contents under / data/mysql/:

[root@master mysql] # ls / data/mysql

Db1 ibdata1 ib_logfile0 ib_logfile1 master.err master.pid mysql mysql-bin.000001 mysql-bin.index test

It is found that the binary log file mysql-bin.000001 has been generated.

Log in to mysql:

[root@master mysql] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.1.73-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Grant the slave password 123abc to a user on the slave MySQL with replication authority:

Mysql > grant replication slave on *. * to 'slave'@'192.168.134.129' identified by' 123abc'

Query OK, 0 rows affected (0.00 sec)

Refresh permissions:

Mysql > flush privileges

Query OK, 0 rows affected (0.01 sec)

Lock the watch first:

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Displays the status of the primary MySQL:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 338 | db1 |

+-+

1 row in set (0.00 sec)

From: 192.168.134.129

Add MySQL to the environment variable:

[root@slave mysql] # vim / etc/profile.d/mypath.sh

Export PATH=$PATH:/usr/local/mysql/bin/

[root@slave mysql] # source / etc/profile.d/mypath.sh

Edit the configuration file:

[root@slave mysql] # vim / etc/my.cnf

Just make sure that the server-id is not the same as the Lord's:

Server-id = 2

(main server-id = 1)

Restart:

[root@slave mysql] # / etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL. SUCCESS!

Also create the library db1 on the

[root@slave mysql] # mysql-e "create database db1"

First copy the 123.sql backed up on the master to the slave / usr/local/mysql directory:

[root@slave mysql] # scp root@192.168.134.128:/usr/local/mysql/123.sql / usr/local/mysql/123.sql

The authenticity of host '192.168.134.128 (192.168.134.128)' can't be established.

RSA key fingerprint is 7d:f3:cc:4e:ae:cb:3c:31:61:d5:13:8e:04:dc:73:02.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.134.128' (RSA) to the list of known hosts.

Root@192.168.134.128's password:

123.sql

Restore 123.sql to db1:

[root@slave mysql] # mysql db1

< 123.sql 保证主从上的数据库一样： 登录mysql先停掉slave： [root@slave mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.73 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Slave stop

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

Configure the master-slave relationship (critical):

Mysql > change master to master_host='192.168.134.128',master_port=3306,master_user='slave',master_password='123abc',master_log_file='mysql-bin.000001',master_log_pos=338

Query OK, 0 rows affected (0.42 sec)

Enable slave:

Mysql > slave start

Query OK, 0 rows affected (0.00 sec)

Check the slave status and show that the configuration is successful with two Yes:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.134.128

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 338

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 251

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

Relay_Log_Space: 406

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:

1 row in set (0.00 sec)

ERROR:

No query specified

4. Test MySQL master-slave: the MySQL master can never operate on the slave, and once some write operations are performed on the slave, the master-slave mechanism will be disordered.

Test 1: delete a table on the master, and delete it from the master:

Master: 192.168.134.128

[root@master mysql] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.1.73-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Unlock the table:

Mysql > unlock tables

Query OK, 0 rows affected (0.01 sec)

Use db1:

Mysql > use db1

Database changed

View the table:

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

23 rows in set (0.00 sec)

Delete the table:

Mysql > drop table help_category

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

22 rows in set (0.00 sec)

From: 192.168.134.129

[root@slave mysql] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 8

Server version: 5.1.73 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Use db1:

Mysql > use db1

Database changed

View the table:

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

22 rows in set (0.00 sec)

You can see that help_category has also been deleted from above.

Test 2: create a table on the master, as well as from:

Master: 192.168.134.128

Create a table:

Mysql > create table tb1 (`id` int (4), `name` char (40)) ENGINE=MyISAM DEFAULT CHARSET=gbk

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | tb1 |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

23 rows in set (0.00 sec)

From: 192.168.134.129

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | servers |

| | slow_log |

| | tables_priv |

| | tb1 |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

23 rows in set (0.00 sec)

You can see the table you just created.

Test 3: delete the library on the master and can no longer be used from the top

Master: 192.168.134.128

Delete the library:

Mysql > drop database db1

Query OK, 23 rows affected (0.01sec)

Mysql > show tables

ERROR 1046 (3D000): No database selected

Check out the library: mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

From: 192.168.134.129

Mysql > show tables

ERROR 1049 (42000): Unknown database 'db1'

Error report: Unknown database 'db1'

Check the library:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

You can see that there is no db1 library.

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.