Master-MasterMySQL replication and installation configuration test

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.