MYSQL+AA Primary Master

MySQL primary master (AA)

Experimental environment

Master-1 192.168.3.1
Master-2 192.168.3.10

Shut down the firewall (two units)
[[Email protected] ~]# service iptables stop
[Email protected] ~]# Setenforce 0

Set Password
[Email protected] ~]# mysqladmin-u root password 123456

First, master-1 operation
(1) Installing the NTP time server
[[email protected] ~]# yum-y install NTP
[Email protected] ~]# vim/etc/ntp.conf
Server 127.127.1.0
Fudge 127.127.1.0 Stratum 8
[[Email protected] ~]# service NTP restart

(2) Modify the configuration file
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Server-id=1
Log-bin=mysql-binlog
[Email protected] ~]# service mysqld restart

(3) Authorized users
[Email protected] ~]# mysql-u root-p123456
Mysql>
mysql> grant replication Slave on * * to ' slave ' @ ' 192.168.3.10 ' identified by ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)
Mysql>
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

(4) Export data
[Email protected] ~]# mysqldump-u root-p123456--all-databases >/root/alldb.sql
[Email protected] ~]# scp/root/alldb.sql [email protected]:/root

Second, master-2 operation

(1) Time synchronization
[Email protected] ~]# yum-y install Ntpdate
[Email protected] ~]# usr/sbin/ntpdate 192.168.3.10
[Email protected] ~]# service ntpd restart

(2) Modify the configuration file
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
server-id=2
Log-bin=mysql-binlog
[Email protected] ~]# service mysqld restart

(3) Authorized users
[Email protected] ~]# mysql-u root-p123456
Mysql>
mysql> grant replication Slave on * * to ' master ' @ ' 192.168.3.1 ' identified by ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)
Mysql>
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

(4) Export data
[Email protected] ~]# mysqldump-u root-p123456--all-databases >/root/alldba.sql
[Email protected] ~]# scp/root/alldba.sql [email protected]:/root

Third, master-1 operation

[[email protected] ~]# mysql-u root-p123456
mysql> show master status;
+---------------------+----------+--------------+------------------+
| file                | Position | binlog_do_db | binlog_ignore_db |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |       337 |               |                   |
+---------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Four, master-2 operation
[Email protected] ~]# mysql-u root-p123456
Mysql> Change Master to
Master_host= ' 192.168.3.1 ',
Master_user= ' slave ',
-master_password= ' 123456 ',
master_log_file=' mysql-binlog.000001 ',
master_log_pos=337;
Query OK, 0 rows affected (0.06 sec)

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

Mysql> Show master status;
+---------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------------+----------+--------------+------------------+
|      mysql-binlog.000001 |              336 |                  | |
+---------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Five, master-1 operation

[Email protected] ~]# mysql-u root-p123456
Mysql> Change Master to
Master_host= ' 192.168.3.10 ',
-master_user= ' master ',
-master_password= ' 123456 ',
master_log_file=' mysql-binlog.000001 ',
master_log_pos=336;
Query OK, 0 rows affected (0.06 sec)

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

Six, respectively master-1 and master-2 above to see if Sql/io thread is turned on

(1) Master-1 view

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.3.10
Master_user:myslave
master_port:3306
Connect_retry:60
master_log_file:mysql-binlog.000001
read_master_log_pos:417
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:335
relay_master_log_file:mysql-binlog.000001
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:417
relay_log_space:491
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)

(2) Master-2 view
Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.3.1
Master_user:myslave
master_port:3306
Connect_retry:60
master_log_file:mysql-binlog.000001
read_master_log_pos:785
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:702
relay_master_log_file:mysql-binlog.000001
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:785
relay_log_space:858
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)

Note: Both ends are yes to succeed

Seven, testing
1, in Master-1 View Library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 rows in Set (0.07 sec)

2. View the library above master-2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 Rows in Set (0.00 sec)

Note: The two data are identical, we now create a library on the master-1 to see if we can synchronize

3. Create a library in Master-1

mysql> CREATE DATABASE Fuzhenyang;
Query OK, 1 row Affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Fuzhenyang |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

Mysql>

4, we check the master-2 above to see if the synchronization is over
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Fuzhenyang |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

Mysql>

At this point we've seen the Fuzhenyang library in sync.

Similarly, create a library above the master-2 to see if it is synchronized on the master-1. mysql master-slave (AA) experiment completed

This article from "Konghuai Sense" blog, reproduced please contact the author!

MYSQL+AA Primary Master