MySQL Dual machine

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Configure two hosts MySQL dual live
Host:
a:192.168.199.2
b:192.168.199.4

One, the installation service, two hosts perform the same operation:

[Email protected] [192.168.199.4] [/tmp]# Tar xvf Mysql-5.6.40-1.el6.x86_64.rpm-bundle.tar
[Email protected] [192.168.199.4] [/tmp]# rpm-ivh mysql-*.rpm--force--nodeps
[Email protected] [192.168.199.4] [/tmp]# cp/usr/share/mysql/my-default.cnf/etc/my.cnf
[Email protected] [192.168.199.4] [/tmp]# service MySQL Start
Starting mysql.logging to '/var/lib/mysql/e2emydb02.err '.
[OK]
MySQL 5.6 After installing the default password is stored in the directory:/root/.mysql_secret
1. Check the password
[Email protected] [192.168.199.4] [/root]# More/root/.mysql_secret
The random password set for the root user at Wed may 11:30:41 2018 (local time): 5EU4P7FZ9QJ_UOJJ
2, use the password login test:
[Email protected] [192.168.199.4] [/root]# mysql-u Root-p
3, change the password:
[Email protected] [192.168.199.4] [/root]# mysqladmin-u root-p Password
Enter Password:
New Password:
Confirm New Password:
4, modify the data to save the directory
Installed by default in the/var/lib/mysql/directory
Modify to a directory of your own definition/etedata
Perform the following actions:
[Email protected] [192.168.199.4] [/root]# service MySQL Stop
[Email protected] [192.168.199.4] [/root]# cp-r/var/lib/mysql/etedata
Back up the original directory
[Email protected] [192.168.199.4] [/root]# Mv/var/lib/mysql/var/lib/mysql.bak
Assigning rights to the/etedata directory
[Email protected] [192.168.199.4] [/root]# chmod 777-r/etedata # #否则启动会报错: Starting MySQL ... The server quit without updating PID file [Failed]a/mysql/e2emydb01.pid].
Error log:/etedata/mysql/e2emydb02.err
Modify/ETC/MY.CNF
[Email protected] [192.168.199.4] [/root]# vi/etc/my.cnf
[Client]
port=3306
Socket=/etedata/mysql/mysql.sock
[Mysqld]
DataDir =/etedata/mysql
Socket=/etedata/mysql/mysql.sock
Sql_mode=no_engine_substitution

Save, restart Service
[Email protected] [192.168.199.4] [/root]# service MySQL Stop
Shutting down MySQL .... [OK]
[Email protected] [192.168.199.4] [/root]# service MySQL Start
Starting MySQL. [OK]
[Email protected] [192.168.199.4] [/tmp]# mysql-u Root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3
Server version:5.6.40 MySQL Community Server (GPL)

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

5, set the server side, the client, the result set is UTF-8
mysql> SET character_set_client = UTF8;
Query OK, 0 rows Affected (0.00 sec)

mysql> SET character_set_results = UTF8;
Query OK, 0 rows Affected (0.00 sec)

Mysql> SET Character_set_database=utf8;
Query OK, 0 rows Affected (0.00 sec)

mysql> CREATE DATABASE Etedb;
Query OK, 1 row affected (0.02 sec)

Mysql> quit

Two configurations from A-->b

At this point, the basic work is done.
Configure A-->b below
1, in a host operation:
[Email protected] [192.168.199.2] [/root] #more/etc/my.cnf
[Client]
port=3306
Socket=/etedata/mysql/mysql.sock

[Mysqld]
Log-bin=mysql-bin
Binlog_format=mixed
Server-id = 1
Read-only=0
Binlog-do-db=etedb
Binlog-ignore-db=information_schema
Binlog-ignore-db=mysql
Binlog-ignore-db=performance_schema
Binlog-ignore-db=test
auto-increment-increment=2
Auto-increment-offset=1

DataDir =/etedata/mysql

Socket=/etedata/mysql/mysql.sock
Restart the server when you are finished
[Email protected] [192.168.199.2] [/root] #service MySQL stop
Shutting down MySQL .... [OK]
[Email protected] [192.168.199.2] [/root] #service MySQL start
Starting MySQL. [OK]
[Email protected] [192.168.199.2] [/root] #mysql-u root-p
Enter Password:
2. Add Users
For user authorization, only 192.168.199.4 can access

GRANT USAGE on . To ' repl_user ' @ ' 192.168.199.2 ' identified by '* * * *with GRANT OPTION;
Grant replication Slave on . to ' repl_user ' @ ' 192.168.199.4 ' identified by '* * *;
Implementation is as follows:
[Email protected] [192.168.199.2] [/root] #mysql-u root-p
Enter Password:
Mysql> GRANT USAGE on . To ' repl_user ' @ ' 192.168.199.2 ' identified by '* * * *with GRANT OPTION;
Query OK, 0 rows Affected (0.00 sec)

mysql> grant replication Slave on . to ' repl_user ' @ ' 192.168.199.4 ' identified by '* * *;
Query OK, 0 rows Affected (0.00 sec)

In the B host test:
[Email protected] [192.168.199.4] [/root] #mysql-h192.168.199.2-urepl_user-p* * * * *
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 7
Server Version:5.6.40-log MySQL Community Server (GPL)

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

3. Get server Initial state
Lock the table in a server execution (MySQL command line):
Mysql> FLUSH TABLES with READ LOCK;
Query OK, 0 rows Affected (0.00 sec)
To perform an export initial configuration on a host:
[Email protected] [192.168.199.2] [/root] #mysqldump--master-data-uroot-p etedb >etedb.sql
Enter Password:
and upload the file to the B server:
[Email protected] [192.168.199.2] [/root] #scp etedb.sql [email protected]:/home/ete
[email protected] ' s password:
Then view the initial state value and unlock it
Mysql> Show Master Status\g
1. Row
file:mysql-bin.000001
position:120
Binlog_do_db:etedb
Binlog_ignore_db:information_schema,mysql,performance_schema,test
Executed_gtid_set:
1 row in Set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)

Note: The two values of red will be used in Server B

4. Execute on Server B:
[Email protected] [192.168.199.4] [/root] #vi/etc/my.cnf
[Client]
port=3306
Socket=/etedata/mysql/mysql.sock

[Mysqld]
DataDir =/etedata/mysql

Log-bin=mysql-bin
Binlog_format=mixed
Server-id= 2

Replicate-do-db=etedb
Replicate-ignore-db=information_schema
Replicate-ignore-db=mysql
Replicate-ignore-db=performance_schema
Replicate-ignore-db=test
Relay_log=mysqld-relay-bin
Log-slave-update=yes

Socket=/etedata/mysql/mysql.sock
Sql_mode=no_engine_substitution

[Email protected] [192.168.199.4] [/root] #service MySQL stop
Shutting down MySQL .... [OK]
[Email protected] [192.168.199.4] [/root] #service MySQL start
Starting MySQL. [OK]
[Email protected] [192.168.199.4] [/root] #mysql-u root-p
Mysql> Change MASTER to
Master_host= ' 192.168.199.2 ',
Master_user= ' Repl_user ',
-Master_password= '****‘,
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Note: The above values are obtained from the master server
Mysql> Show Slave Status\g
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.199.2
Master_user:repl_user
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:120
relay_log_file:mysqld-relay-bin.000003
relay_log_pos:283
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes # # These two values are yes to indicate OK
Slave_sql_running:yes # #
Replicate_do_db:etedb
Replicate_ignore_db:information_schema,mysql,performance_schema,test
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:120
relay_log_space:457
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
Master_uuid:dd4d3f58-5fcb-11e8-8e32-6c92bf5e6ba0
Master_info_file:/etedata/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)
Import the initial file that you just synced from a server:
[Email protected] [192.168.199.4] [/home/ete] #cp etedb.sql/rootbr/>[email protected][192.168.199.4] [/home/ete] #cd
[Email protected][192.168.199.4] [/root] #mysql-u root-p etedb<etedb.sql
Enter Password:
[email protected][192.168.199.4] [/root] #service MySQL stop
Shutting down MySQL .... [OK]
[email protected][192.168.199.4] [/root] #service MySQL start
Starting MySQL. [OK]

You can then test the
Perform the build table and and interpolation on the A host:
mysql> use Etedb;
Database changed
Mysql> CREATE TABLE A (id int);
Query OK, 0 rows affected (0.01 sec)

Mysql> INSERT into a values (1);
Query OK, 1 row Affected (0.00 sec)
In the B host authentication:
mysql> use Etedb;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables;
+-----------------+
| Tables_in_etedb |
+-----------------+
| A |
+-----------------+
1 row in Set (0.00 sec)

Mysql> select * from A;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)

Third, configuration B-->a

1, in the B host execution:
Mysql> GRANT USAGE on . To ' repl_user ' @ ' 192.168.199.4 ' identified by '* * * *with GRANT OPTION;
Query OK, 0 rows Affected (0.00 sec)

mysql> grant replication Slave on . to ' repl_user ' @ ' 192.168.199.2 ' identified by '* * *;
Query OK, 0 rows Affected (0.00 sec)
2, modify the configuration file B:
[email protected] [192.168.199.4] [/root] #vi/etc/my.cnf
[Client]
port=3306
Socket=/etedata/mysql/mysql.sock

[Mysqld]
DataDir =/etedata/mysql

Log-bin=mysql-bin
Binlog_format=mixed
Server-id= 2

Read-only=0
Binlog-do-db=etedb
Binlog-ignore-db=information_schema
Binlog-ignore-db=test
Binlog-ignore-db=mysql
Binlog-ignore-db=performance_schema
auto-increment-increment=2
auto-increment-offset=2

Replicate-do-db=etedb
Replicate-ignore-db=information_schema
Replicate-ignore-db=mysql
Replicate-ignore-db=performance_schema
Replicate-ignore-db=test
Relay_log=mysqld-relay-bin
Log-slave-update=yes

Socket=/etedata/mysql/mysql.sock
Sql_mode=no_engine_substitution
3. View initial state parameters
Mysql> Show Master Status\g
1. Row
file:mysql-bin.000005
position:120
Binlog_do_db:etedb
Binlog_ignore_db:information_schema,test,mysql,performance_schema
Executed_gtid_set:
1 row in Set (0.00 sec)
4, in a server execution:
[email protected] [192.168.199.2] [/root] #vi/etc/my.cnf
[Client]
port=3306
Socket=/etedata/mysql/mysql.sock

[Mysqld]

Log-bin=mysql-bin
Binlog_format=mixed
Server-id = 1

Read-only=0
Binlog-do-db=etedb

Binlog-ignore-db=information_schema
Binlog-ignore-db=mysql
Binlog-ignore-db=performance_schema
Binlog-ignore-db=test
auto-increment-increment=2
Auto-increment-offset=1

Replicate-do-db=etedb
Replicate-ignore-db=information_schema
Replicate-ignore-db=test
Replicate-ignore-db=mysql
Replicate-ignore-db=performance_schema
Relay_log=mysqld-relay-bin
Log-slave-update=yes

DataDir =/etedata/mysql

Socket=/etedata/mysql/mysql.sock
In database execution:
[email protected] [192.168.199.2] [/root] #mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server Version:5.6.40-log MySQL Community Server (GPL)

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Change MASTER to
Master_host= ' 192.168.199.4 ',
Master_user= ' Repl_user ',
-Master_password= '****‘,
Master_log_file= ' mysql-bin.000005 ',
master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
To restart the server:
[email protected][192.168.199.2] [/root] #service MySQL stop
Shutting down MySQL .... [OK]
[email protected][192.168.199.2] [/root] #service MySQL start
Starting MySQL. [OK]
To view the status of a database:
Mysql> Show Slave Status\g
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.199.4
Master_user:repl_user
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000005
read_master_log_pos:1324
relay_log_file:mysqld-relay-bin.000003
relay_log_pos:1487
relay_master_log_file:mysql-bin.000005
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:etedb
Replicate_ignore_db:information_schema,test,mysql,performance_schema
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:1324
relay_log_space:1661
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
master_uuid:2c61b0aa-5fe9-11e8-8ef1-6c92bf5e0138
Master_info_file:/etedata/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)
Then do two-way testing:
In B Server execution:

Mysql> CREATE TABLE B (id int);
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into B values (2);
Query OK, 1 row Affected (0.00 sec)
In a view:
[email protected] [192.168.199.2] [/root] #mysql-u root-p
Enter Password:
Mysql> Show tables;
+-----------------+
| Tables_in_etedb |
+-----------------+
| A |
| B |
+-----------------+
2 rows in Set (0.00 sec)

Mysql> SELECT * from B;
+------+
| ID |
+------+
| 2 |
+------+
1 row in Set (0.00 sec)

IV: Finally, create a remotely accessible user on both servers

Mysql> GRANT USAGE on . To ' ete ' @ '% ' identified by '* *' with GRANT OPTION;
Query OK, 0 rows Affected (0.00 sec)

Mysql> GRANT all privileges on . To ' ete ' @ '% ' identified by '* *' with GRANT OPTION;
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

MySQL Dual machine

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

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

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

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

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

MySQL Dual machine

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support

MySQL Dual machine

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

Trending Topic

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support