Master-MasterMySQL replication and installation configuration test

Master-MasterMySQL replication and installation configuration test

 

I. Architecture

 

Ii. Install Mysql server

The following uses the configuration of DB1 (192.168.0.10) as an example. The configuration of DB2 is basically the same. You only need to modify server_id = 2 in my. cnf.

1. Install mysql-server

Db1 # apt-get install mysql-server

Note:

? If the installation is faulty, execute apt-getupdate to update the source and try again.

? During installation, enter the root password and remember (123456)

2. Verify Database Installation

Db1 # mysql-p Enter password: Welcome to the MySQL monitor. Commands end with; or \ g. Your MySQL connection id is 42 Server version: 5.5.38-0ubuntu0. 12.04.1 (Ubuntu)   Copyright (c) 2000,201 4, 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. (Db1) mysql> show databases; + -------------------- + | Database | + -------------------- + | Information_schema | | Mysql | | Performance_schema | + -------------------- + 3 rows in set (0.00 sec) (Db1) mysql>

3. Create a Test Database

Create database /*! 32312 if not exists */'test '/*! 40100 default character set latin1 */; USE 'test '; /* Table structure for table 'user '*/ Drop table if exists 'user '; Create table 'user '( 'Name' varchar (16) default null, 'Age' int (11) DEFAULT NULL ) ENGINE = InnoDB default charset = utf8;

This step is omitted

4. my. cnf Configuration Modification

The red section below shows the modified configuration.

The MySQL database server configuration file. # # You can copy this to one: #-"/Etc/mysql/my. cnf" to set global options, #-"~ /. My. cnf "to set user-specific options. # # One can use all long options that the program supports. # Run program with -- help to get a list of available options and # -- Print-defaults to see which it wocould actually understand and use. # # For explanations see # Http://dev.mysql.com/doc/mysql/en/server-system-variables.html   # This will be passed to all mysql clients # It has been reported that passwords shoshould be enclosed with ticks/quotes # Escpecially if they contain "#" chars... # Remember to edit/etc/mysql/debian. cnf when changing the socket location. [Client] Port = 3306 Socket =/var/run/mysqld. sock # Here is entries for some specific programs # The following values assume you have at least 32 M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [Mysqld_safe] Socket =/var/run/mysqld. sock Nice = 0 [Mysqld] # # * Basic Settings # User = mysql Pid-file =/var/run/mysqld. pid Socket =/var/run/mysqld. sock Port = 3306 Basedir =/usr Datadir =/var/lib/mysql Tmpdir =/tmp Lc-messages-dir =/usr/share/mysql Skip-external-locking # # Instead of skip-networking the default is now to listen only on # Localhost which is more compatible and is not less secure. # Bind-address = 127.0.0.1 Bind-address = 0.0.0.0 # # * Fine Tuning # Key_buffer = 16 M Max_allowed_packet = 16 M Thread_stack = 192 K Thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # The first time they are touched Myisam-recover = BACKUP # Max_connections = 100 # Table_cache = 64 # Thread_concurrency = 10 # # * Query Cache Configuration # Query_cache_limit = 1 M Query_cache_size = 16 M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! # General_log_file =/var/log/mysql. log # General_log = 1 # # Error log-shoshould be very few entries. # Log_error =/var/log/mysql/error. log # # Here you can see queries with especially long duration # Log_slow_queries =/var/log/mysql/mysql-slow.log # Long_query_time = 2 # Log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # Note: if you are setting up a replication slave, see README. Debian about # Other settings you may need to change. # Server-id = 1 # Log_bin =/var/log/mysql/mysql-bin.log Expire_logs_days = 10 Max_binlog_size = 100 M # Binlog_do_db = include_database_name # Binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10 MB datafile in/var/lib/mysql /. # Read the manual for more InnoDB related options. There are done! # # * Security Features # # Read the manual, too, if you want chroot! # Chroot =/var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca ". # # Ssl-ca =/etc/mysql/cacert. pem # Ssl-cert =/etc/mysql/server-cert.pem # Ssl-key =/etc/mysql/server-key.pem Server_id = 1 Log_bin =/var/log/mysql/mysql-bin.log Log_bin_index =/var/log/mysql/mysql-bin.log.index Relay_log =/var/log/mysql-relay-bin Relay_log_index =/var/log/mysql/mysql-relay-bin.index Expire_logs_days = 10 Max_binlog_size = 100 M Log_slave_updates = 1 Auto_increment_increment = 2 Auto_increment_offset = 1 [Mysqldump] Quick Quote-names Max_allowed_packet = 16 M [Mysql] # No-auto-rehash # faster start of mysql but no tab completition [Isamchk] Key_buffer = 16 M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '. cnf', otherwise they'll be ignored. # ! Includedir/etc/mysql/conf. d/

5. Create users with three roles

Table 1

Role	Function	Permission
Monitor user	MMM (Multi-Master replication manager of MYSQL) monitors the health status of each Master	REPLICATION CLIENT
Agent user	MMM proxy is used to set read-only attributes, replication master, etc.	SUPER, replication client, PROCESS
Replicate user	Used for copying	REPLICATION SLAVE

Table 2

(Db1) mysql> grant replication client on *. * TO 'mmm _ monitor' @ '2014. 29.198.150 'identified BY '20140901 '; Query OK, 0 rows affected (0.00 sec)   (Db1) mysql> grant super, replication client, process on *. * TO 'mmm _ agent' @ '42. 96. %. % 'identified by '123 '; Query OK, 0 rows affected (0.00 sec)   (Db1) mysql> grant replication slave on *. * TO 'replicase' @ '42. 96. %. % 'identified by '123 '; Query OK, 0 rows affected (0.00 sec)

3. Synchronize DB1 and DB2 Databases

Assume that DB1 contains the correct data (even an empty database) for direct data synchronization between DB1 and DB2.

1. Execute data export on the server where DB1 is located

(Db1) mysql> flush tables with read lock; (Db1) mysql> show master status; + ------------------ + ---------- + -------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ---------- + -------------- + ------------------ + | Mysql-bin.000002 | 616 | + ------------------ + ---------- + -------------- + ------------------ + 1 row in set (0.00 sec) (Db1) mysql>

2. Open a command window to export data.

(Db1) # mysqldump-u root-p -- all-databases>/tmp/database-backup. SQL

3. Unlock the first window

(Db1) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)   (Db1) mysql>

4. Import the data exported by DB1 to DB2

1) copy to DB2

(Db1) # scp database-backup. SQL root@192.168.0.11:/tmp/ The authenticity of host' 192. 168.0.11 (192.168.0.11) 'can't be established. ECDSA key fingerprint is 55: 84: 03: 9e: d9: 74: cc: cd: 03: 59: 23: 3f: df: d9: 77: a5. Are you sure you want to continue connecting (yes/no )? Yes Warning: Permanently added '192. 168.0.11 '(ECDSA) to the list of known hosts. Root@192.168.0.11's password: Database-backup. SQL 100% 528KB 527.9KB/s (Db1):/tmp #

2) Import DB2

(Db2) # mysql-u root-p </tmp/database-backup. SQL Enter password: (Db2 )#

3) Application Permissions

(Db2) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

4) Copy debian. cnf

Copy/etc/mysql/debian. cnf from DB1 to DB2. This file is used to start and stop mysql.

(Db1) # scp debian. cnf root@192.168.0.11:/tmp/

Back up the original debian. cnf on DB2 and use the debian. cnf copied from DB1.

(Db2) # mv/etc/mysql/debian. cnf/etc/mysql/debian. cnf. orign (Db2) # cp-f debian. cnf/etc/mysql/debian. cnf

After completing the preceding steps, you can start to copy configurations.

Iv. Copy configurations

1. Run the following command on DB2:

(Db2) mysql> change master to master_host = '2017. 168.0.10 ', master_port = 3306, master_user = 'replicase', master_password = '000000', master_log_file = 'mysql-bin.000002', master_log_pos = 123456; Query OK, 0 rows affected (0.04 sec)

Note: master_log_file = 'mysql-bin.000002 ', master_log_pos = 616 information is executed on db1.

Mysql> show master status;

2. Start SLAVE on DB2

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

3. Check the replication process on DB2

(Db2) mysql> show slave status \ G * *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.10 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 616 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 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: 616 Relay_Log_Space: 409 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 1 row in set (0.00 sec)

4. copy configuration from DB2 to DB1

1) DB2 status

(Db2) mysql> show master status; + ------------------ + ---------- + -------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ---------- + -------------- + ------------------ + | Mysql-bin.000002 | 107 | + ------------------ + ---------- + -------------- + ------------------ + 1 row in set (0.01 sec) Mysql>

2) configure, start, and check DB1 Replication

(Db1) mysql> change master to master_host = '192. 168.0.11 ', master_port = 192, master_user = 'replicase ', -> Master_password = '000000', master_log_file = 'mysql-bin.000002 ', master_log_pos = 123456; Query OK, 0 rows affected (0.05 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.0.11 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 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: 107 Relay_Log_Space: 409 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: 2 1 row in set (0.00 sec)   Mysql>

After completing the MASTER-MASTER replication configuration, perform the following tests.

V. Copy Verification

1. Insert a piece of data on DB1

(Db1) mysql> select * from user; Empty set (0.00 sec) (Db1) mysql> insert into user (name, age) values ('user1', 20 ); Query OK, 1 row affected (0.03 sec) (Db1) mysql>

2. Check on DB2

(Db2) mysql> select * from user; Empty set (0.00 sec) (Db2) mysql> select * from user; + ------- + ------ + | Name | age | + ------- + ------ + | User1 | 20 | + ------- + ------ + 1 row in set (0.00 sec) (Db2) mysql>

The record (user1, 20) inserted on the surface of DB1 has been copied to DB2.

3. Insert a piece of data into DB2

(Db2) mysql> insert into user (name, age) values ('user2', 30 ); Query OK, 1 row affected (0.02 sec

4. Check on DB1

(Db1) mysql> select * from user; + ------- + ------ + | Name | age | + ------- + ------ + | User1 | 20 | | User2 | 30 | + ------- + ------ + 2 rows in set (0.00 sec)

The record (user2, 30) inserted in DB2 has been copied to db1.

On the above test surface, the MM configuration of DB1 <-----> DB2 is successful.