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

The following mainly brings you MySQL master and standby detailed tutorials of operating CDH Metabase. I hope these contents can bring you practical use, which is also the main purpose of this article of editing and operating CDH Metabase MySQL master and standby detailed tutorials. All right, don't talk too much nonsense, let's just read the following.

Content Overview

1.Master and Slave configuration

two。 Build master-slave replication

3. Master-slave replication verification

Test environment

1. Two Linux CVMs (172.31.10.118 (master) / 172.31.5.190) with CentOS6.5 operating system

2.MySQL5.1.73

3. Adopt root user operation

Precondition

1. The two MySQL versions must be consistent

two。 Two MySQL are installed and there is no data

3. Bin-log logs must be enabled for the primary MySQL

2.MySQL master-slave replication

2.1Master and Slave configuration

Profile description:

Log-bin: enable binary log, log file prefix

Server-id: the unique identity of the database service ensures that the identity is not duplicated. It is generally set to the end of the CVM ip.

Binlog-format: sets the format of Mysql binlog log (format: Statement, MIXED, ROW). MySQL uses Statement by default, and MIXED is recommended.

Master Master Service configuration (172.31.10.118)

Modify the / etc/my.conf file to add the following configuration

[root@ip-172-31-10-118 cloudera-scm-server] # vim / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-binserver-id=118binlog_format= MIXED [mysqld _ safe] log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Slave service configuration (172.31.5.190)

Modify the / etc/my.conf file to add the following configuration

[root@ip-172-31-5-190] # vim / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-binserver-id= 190 [mysqld _ safe] log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

[root@ip-172-31-5-190] # service mysqld restartStopping mysqld: [OK] Starting mysqld: [OK] [root@ip-172-31-5-190] #

2.1 build master-slave replication

Create a mysnc user on the Master (172.31.10.118) primary MySQL

User name: mysync password: mysync

GRANT REPLICATION SLAVE ON *. * TO 'mysync'@'172.31.%' IDENTIFIED BY' mysync';FLUSH PRIVILEGES

The mysync user must have REPLICATION SLAVE privileges. Explain 172.31%. This configuration indicates the mysync user's CVM, where% is a wildcard, which means that all Server whose IP starts with 172.31 can log in to the Master master CVM using mysync users. You can also specify a fixed IP.

Command line operation

[root@ip-172-31-10-118] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 11Server version: 5.1.73-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > GRANT REPLICATION SLAVE ON *. * TO 'mysync'@'172.31.%' IDENTIFIED BY' mysync';Query OK, 0 rows affected (0.00 sec) mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)

two。 View Master (172.31.10.118) MySQL binary log File and Position

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 1121 | +-+ 1 row in set (sec) mysql >

3. Execute the following SQL on the Slave slave MySQL

Change master tomaster_host='172.31.10.118',master_user='mysync',master_password='mysync',master_log_file='mysql-bin.000003',master_log_pos=1121

Command line execution

# mysql- uroot-p...Server version: 5.1.73-log Source distribution...mysql > change master to-> master_host='172.31.10.118',-> master_user='mysync',-> master_password='mysync',-> master_log_file='mysql-bin.000003',-> master_log_pos=1121;Query OK, 0 rows affected (0.02 sec) mysql >

4. Execute the command on the Slave slave MySQL to start synchronization

Mysql > start slave;Query OK, 0 rows affected (0.00 sec) mysql >

5. View Slave status on Slave MySQL

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.31.10.118 Master_User: mysync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1121 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000003 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: 1121 Relay_Log_Space: 407 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: 0Master_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) mysql >

Note: if the annotated part of the above figure shows "Yes", it means that the master-slave replication is built successfully.

2.3 Master-Slave replication Verification

Log in to the Master primary MySQL to execute SQL

two。 Log in to Slave and execute SQL from MySQL

3. Execute SQL on the Master master MySQL

Mysql > create database test;Query OK, 1 row affected (0.00 sec) mysql > use test;Database changedmysql > create table table1 (id int, name varchar (32)); Query OK, 0 rows affected (0.01 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > show tables +-+ | Tables_in_test | +-+ | table1 | +-+ 1 row in set (0.00 sec) mysql >

4. Perform a SQL view on Slave from the MySQL

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > use test Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables;+-+ | Tables_in_test | +-+ | table1 | +-+ 1 row in set (0.00 sec) mysql >

Through the above tests, the libraries and tables created by the Master master MySQL are normally synchronized to the Slave slave MySQL.

3. Remarks

How to stop and delete master-slave synchronization, execute the following SQLmysql > stop slave;Query OK, 0 rows affected (0.00 sec) mysql > reset slave;Query OK, 0 rows affected (0.00 sec) mysql > on the Slave slave sec

Note: after performing the above operation, you need to restart the MySQL service.

For the above operation of the CDH Metabase MySQL master and standby detailed tutorials, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.