MySQL Master master Mutual Standby Quick Configuration (memo retention)

Subsequent additions:

keepalived implementing MySQL Failover

Heartbeat+drbd+mysql High Availability

MMM build MySQL high-availability cluster

1. Configuring the Environment

[Email protected]_1 ~]# uname-a
Linux wdds_1 2.6.32-504.el6.x86_64 #1 SMP Wed Oct 04:27:16 UTC x86_64 x86_64 x86_64 gnu/linux[[email protected]_1 ~]# ifconfig eth0 |grep "inet addr:"
inet addr:10.0.0.61 bcast:10.0.0.255 mask:255.255.255.0

[Email protected]_2 ~]# ifconfig eth0|grep "inet addr:"
inet addr:10.0.0.62 bcast:10.0.0.255 mask:255.255.255.0

2. Install MySQL

Yum Install mysql*

3. Configuring the My.cnf File

Node One:

[Email protected]_1 ~]# cat/etc/my.cnf
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Server-id = 1
Log-bin = Mysql-relay-bin
Relay-log = Mysql-relay-bin
Replicate-wild-ignore-table = mysql.%
Replicate-wild-ignore-table = test.%
Replicate-wild-ignore-table = information_schema.%

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

Node Two:

[Email protected]_2 ~]# cat/etc/my.cnf
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Server-id = 2
Log-bin = Mysql-relay-bin
Relay-log = Mysql-relay-bin
Replicate-wild-ignore-table = mysql.%
Replicate-wild-ignore-table = test.%
Replicate-wild-ignore-table = information_schema.%

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

4, before synchronization, you need to manually synchronize the database to ensure database consistency

You need to lock the table before synchronizing:

Mysql> FLUSH TABLES with READ LOCK;
Query OK, 0 rows Affected (0.00 sec)

Synchronization method:

1) export using the Mysqldump tool and then import to another node

2) directly package the/var/lib/mysql file or other MySQL date file (as this is the test package directly)

Tar zcvf Mysql.tar.gz/var/lib/mysql then SCP to another node.

5. Create a replicated user on two nodes:

Node One:

mysql> grant replication Slave on * * to ' repl_user ' @ '% ' identified by ' 123.com ';

Mysql> Show master status;
+------------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------------+----------+--------------+------------------+
|     mysql-relay-bin.000004 |              1231 |                  | |
+------------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Node Two:

mysql> grant replication Slave on * * to ' repl_user ' @ ' 10.0.0.61 ' identified by ' 123.com ';

Mysql> Show master status;
+------------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------------+----------+--------------+------------------+
| mysql-relay-bin.000002 |              480 |                  | |
+------------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

6. Configure the respective master node and:

Node One:

mysql> Change Master to master_host= ' 10.0.0.62 ', master_user= ' repl_user ', master_password= ' 123.com ', Master_log_ File= ' mysql-relay-bin.000002 ', master_log_pos=480;

Start: Mysql>start slave;

To view the status after startup:

mysql> show Slave status\g;
1. Row ***************************
slave_io_state:waiting for Master to send event
master_host:10.0.0.62
Master_user:repl_user
master_port:3306
Connect_retry:60
master_log_file:mysql-relay-bin.000002
read_master_log_pos:480
relay_log_file:mysql-relay-bin.000002
relay_log_pos:367
relay_master_log_file:mysql-relay-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:mysql.%,test.%,information_schema.%
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:480
relay_log_space:522
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)

Node Two:

mysql> Change Master to master_host= ' 10.0.0.61 ', master_user= ' repl_user ', master_password= ' 123.com ', Master_log_ file=' mysql-relay-bin.000004 ', master_log_pos=480;

Start: Mysql>start slave;

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.0.0.61
Master_user:repl_user
master_port:3306
Connect_retry:60
master_log_file:mysql-relay-bin.000004
read_master_log_pos:1231
relay_log_file:mysql-relay-bin.000004
relay_log_pos:1227
relay_master_log_file:mysql-relay-bin.000004
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:mysql.%,test.%,information_schema.%
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:1231
relay_log_space:1488
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.01 sec)

7. Test: Create a database or table on two nodes, and see if it has been created on another node.

Create a database test100 character set UTF8;

View database show databases;

MySQL Master master Mutual Standby Quick Configuration (memo retention)