Mysql+ab Master

The experiment requires the environment:

Two Linux servers

master:192.168.3.10

slave:192.168.3.11
The premise is that both have MySQL installed.

First, configure the MySQL master server

1. Modify or add the following in/ETC/MY.CNF:
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Server-id=1
Log-bin=mysql-binlog
Log-slave-updates=true

Restarting the MySQL server
[Email protected] ~]# service mysqld restart

2. Create Replication users
[Email protected] ~]# mysql-u root-p123456
mysql> grant replication Slave on * * to ' slave ' @ ' 192.168.3.11 ' identified by ' 123456 ';
mysql> flush Privileges; For replicated slave servers (read binary log events from the primary server)

3. Get information about Master DB

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

Used for slave DB connection, record values for file and position
Mysql> FLUSH TABLES with READ LOCK;
Query OK, 0 rows Affected (0.00 sec)

4. Export the database and synchronize to the
[Email protected] ~]# mysqldump-u root-p123456--all-databases >/root/allbackup.sql
[Email protected] ~]# scp/root/allbackup.sql [email protected]:/root/

Second, the configuration on the MySQL Slave
1. Test from the Library connection main Library, if the connection is successful, the main library configuration is successful
[Email protected] ~]# mysql-u slave-p123456-h 192.168.3.10

2. Import the database files that have just been sent from the master server
[Email protected] ~]# Mysql-u root-p </root/allbackup.sql

3. Modify the MySQL configuration file
[Email protected] ~]# VIM/ETC/MY.CNF
Server-id = 2
Relay-log=relay-log-bin
Relay-log-index=slave-relay-bin.index

Note: The master/slave Server-id number cannot be the same

4. Restart the database
[Email protected] ~]# service mysqld restart

5. In slave server Licensing, start from the library, the master-slave library data synchronization
[Email protected] ~]# mysql-u root-p123456
Mysql> Change MASTER to
Master_host= ' 192.168.3.10 ',
Master_user= ' slave ',
-master_password= ' 123456 ',
master_log_file=' mysql-binlog.000001 ',
master_log_pos=426;
Query OK, 0 rows affected (0.05 sec)
mysql> start slave;
Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.3.10
Master_user:slave
master_port:3306
Connect_retry:60
master_log_file:mysql-binlog.000001
read_master_log_pos:426
relay_log_file:relay-log-bin.000002
relay_log_pos:238
relay_master_log_file:mysql-binlog.000001
Slave_io_running:Yes to ensure Yes
Slave_sql_running:Yes to ensure 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:426
relay_log_space:238
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
1 row in Set (0.00 sec)

Third, the test is successful
You can create a database in master db, or a table, to Slave db, and if the configuration succeeds, it can be synchronized successfully.

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

View currently existing libraries from slave
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 Rows in Set (0.00 sec)
Note that the data in both is consistent

Main Library Create Library
mysql> CREATE DATABASE Fuzhenyang;

Open from library to see if syncing

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

Now you've seen the Fuzhenyang library synchronized.
Experiment finished.

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

Mysql+ab Master