MySQL - Réplication Master-Master

1 yum install mysql-server

1 chkconfig mysqld on
2 
3 service mysqld start

 1 /usr/bin/mysql_secure_installation
 2 
 3 
 4 
 5 
 6 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
 7       SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 8 
 9 
10 In order to log into MySQL to secure it, we'll need the current
11 password for the root user.  If you've just installed MySQL, and
12 you haven't set the root password yet, the password will be blank,
13 so you should just press enter here.
14 
15 Enter current password for root (enter for none): 
16 OK, successfully used password, moving on...
17 
18 Setting the root password ensures that nobody can log into the MySQL
19 root user without the proper authorisation.
20 
21 Set root password? [Y/n] 
22 New password: 
23 Re-enter new password: 
24 Password updated successfully!
25 Reloading privilege tables..
26  ... Success!
27 
28 
29 By default, a MySQL installation has an anonymous user, allowing anyone
30 to log into MySQL without having to have a user account created for
31 them.  This is intended only for testing, and to make the installation
32 go a bit smoother.  You should remove them before moving into a
33 production environment.
34 
35 Remove anonymous users? [Y/n] 
36  ... Success!
37 
38 Normally, root should only be allowed to connect from 'localhost'.  This
39 ensures that someone cannot guess at the root password from the network.
40 
41 Disallow root login remotely? [Y/n] 
42  ... Success!
43 
44 By default, MySQL comes with a database named 'test' that anyone can
45 access.  This is also intended only for testing, and should be removed
46 before moving into a production environment.
47 
48 Remove test database and access to it? [Y/n] 
49  - Dropping test database...
50  ... Success!
51  - Removing privileges on test database...
52  ... Success!
53 
54 Reloading the privilege tables will ensure that all changes made so far
55 will take effect immediately.
56 
57 Reload privilege tables now? [Y/n] 
58  ... Success!
59 
60 Cleaning up...
61 
62 
63 
64 All done!  If you've completed all of the above steps, your MySQL
65 installation should now be secure.
66 
67 Thanks for using MySQL!

1 #replication
2 server-id               = 10
3 log_bin                 = mysql-bin
4 binlog-ignore-db=information_schema
5 binlog-ignore-db=mysql
6 replicate-ignore-db=information_schema
7 replicate-ignore-db=mysql

1 service mysqld restart

 1 mysql -u root -p
 2 Enter password:
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 2
 5 Server version: 5.1.73-log Source distribution
 6 
 7 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> create user 'replication_user'@'%' identified by 'mypassword';
16 Query OK, 0 rows affected (0.00 sec)
17 
18 mysql> grant replication slave on . to 'replication_user'@'%';
19 Query OK, 0 rows affected (0.00 sec)
20 
21 mysql> show master status;
22 +------------------+----------+--------------+--------------------------+
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
24 +------------------+----------+--------------+--------------------------+
25 | mysql-bin.000001 |      357 |              | information_schema,mysql |
26 +------------------+----------+--------------+--------------------------+
27 1 row in set (0.00 sec)

1 #replication
2 server-id               = 20
3 log_bin                 = mysql-bin
4 binlog-ignore-db=information_schema
5 binlog-ignore-db=mysql
6 replicate-ignore-db=information_schema
7 replicate-ignore-db=mysql

1 service mysqld restart

 1 

 2 mysql -u root -p
 3 Enter password:
 4 Welcome to the MySQL monitor.  Commands end with ; or \g.
 5 Your MySQL connection id is 2
 6 Server version: 5.1.73-log Source distribution
 7 
 8 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 9 
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13 
14 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15 
16 mysql> create user 'replication_user'@'%' identified by 'mypassword';                                                                                                        

17 Query OK, 0 rows affected (0.00 sec)
18 
19 mysql> grant replication slave on . to 'replication_user'@'%';
20 Query OK, 0 rows affected (0.00 sec)
21 
22 mysql>  slave stop;
23 Query OK, 0 rows affected, 1 warning (0.00 sec)
24 
25 mysql> CHANGE MASTER TO MASTER_HOST = '192.168.5.40', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'mypassword', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 357;
26 Query OK, 0 rows affected (0.04 sec)
27 
28 mysql> slave start;
29 Query OK, 0 rows affected (0.00 sec)
30 
31 mysql> show master status;
32 +------------------+----------+--------------+--------------------------+
33 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
34 +------------------+----------+--------------+--------------------------+
35 | mysql-bin.000001 |      357 |              | information_schema,mysql |
36 +------------------+----------+--------------+--------------------------+
37 1 row in set (0.00 sec)

1 slave stop;
2 Query OK, 0 rows affected, 1 warning (0.00 sec)
3 
4 mysql> CHANGE MASTER TO MASTER_HOST = '192.168.5.23', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'mypassword', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 357;
5 Query OK, 0 rows affected (0.03 sec)
6 
7 mysql> slave start;
8 Query OK, 0 rows affected (0.00 sec)

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 +--------------------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> create database DB_CreateOnMySQL1;
11 Query OK, 1 row affected (0.00 sec)
12 
13 mysql> show databases;
14 +--------------------+
15 | Database           |
16 +--------------------+
17 | information_schema |
18 | DB_CreateOnMySQL1  |
19 | mysql              |
20 +--------------------+
21 3 rows in set (0.00 sec)
22 
23 mysql> create table DB_CreateOnMySQL1.tableOn1 (id varchar(10));
24 Query OK, 0 rows affected (0.02 sec)
25 
26 mysql> use DB_CreateOnMySQL1
27 Database changed
28 mysql> show tables;
29 +-----------------------------+
30 | Tables_in_DB_CreateOnMySQL1 |
31 +-----------------------------+
32 | tableOn1                    |
33 +-----------------------------+
34 1 row in set (0.00 sec)

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | DB_CreateOnMySQL1  |
 7 | mysql              |
 8 +--------------------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> use DB_CreateOnMySQL1
12 Reading table information for completion of table and column names
13 You can turn off this feature to get a quicker startup with -A
14 
15 Database changed
16 mysql> show tables;
17 +-----------------------------+
18 | Tables_in_DB_CreateOnMySQL1 |
19 +-----------------------------+
20 | tableOn1                    |
21 +-----------------------------+
22 1 row in set (0.00 sec)

 1 mysql> create table tableOn2 (id varchar(10));
 2 Query OK, 0 rows affected (0.03 sec)
 3 
 4 mysql> show tables;
 5 +-----------------------------+
 6 | Tables_in_DB_CreateOnMySQL1 |
 7 +-----------------------------+
 8 | tableOn1                    |
 9 | tableOn2                    |
10 +-----------------------------+
11 2 rows in set (0.00 sec)
12 
13 mysql> create database DB_CreateOnMySQL2;
14 Query OK, 1 row affected (0.00 sec)
15 
16 mysql> show databases;
17 +--------------------+
18 | Database           |
19 +--------------------+
20 | information_schema |
21 | DB_CreateOnMySQL1  |
22 | DB_CreateOnMySQL2  |
23 | mysql              |
24 +--------------------+
25 4 rows in set (0.00 sec)
26 
27 mysql> create table DB_CreateOnMySQL2.table2On2 (id varchar(10));
28 Query OK, 0 rows affected (0.01 sec)
29 
30 mysql> use DB_CreateOnMySQL2
31 Reading table information for completion of table and column names
32 You can turn off this feature to get a quicker startup with -A
33 
34 Database changed
35 mysql> show tables;                                                                                                                                                          

36 +-----------------------------+
37 | Tables_in_DB_CreateOnMySQL2 |
38 +-----------------------------+
39 | table2On2                   |
40 +-----------------------------+
41 1 row in set (0.00 sec)

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | DB_CreateOnMySQL1  |
 7 | DB_CreateOnMySQL2  |
 8 | mysql              |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 mysql> use DB_CreateOnMySQL2
13 Reading table information for completion of table and column names
14 You can turn off this feature to get a quicker startup with -A
15 
16 Database changed
17 mysql> show tables;
18 +-----------------------------+
19 | Tables_in_DB_CreateOnMySQL2 |
20 +-----------------------------+
21 | table2On2                   |
22 +-----------------------------+
23 1 row in set (0.00 sec)