Shulou(Shulou.com)05/31 Report--

How to carry out MySQL dual-computer operation, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Configure two hosts for MySQL dual active

Host:

A:192.168.199.2

B:192.168.199.4

First, install the service, and the two hosts perform the same operation:

Root@ e2emydb02 [192.168.199.4] [/ tmp] # tar xvf MySQL-5.6.40-1.el6.x86_64.rpm-bundle.tar

Root@ e2emydb02 [192.168.199.4] [/ tmp] # rpm-ivh MySQL-*.rpm-- force-- nodeps

Root@ e2emydb02[192.168.199.4] [/ tmp] # cp / usr/share/mysql/my-default.cnf / etc/my.cnf

Root@ e2emydb02[192.168.199.4] [/ tmp] # service mysql start

Starting MySQL.Logging to'/ var/lib/mysql/e2emydb02.err'.

[OK]

The default password for installation after mysql 5.6is stored in the directory: / root/.mysql_secret

1. Check the password

Root@ e2emydb02[192.168.199.4] [/ root] # more / root/.mysql_secret

The random password set for the root user at Wed May 23 11:30:41 2018 (local time): 5eU4P7fz9Qj_UojJ

2. Login test with this password:

Root@ e2emydb02 [192.168.199.4] [/ root] # mysql-u root-p

3. Change the password:

Root@ e2emydb02 [192.168.199.4] [/ root] # mysqladmin-u root-p password

Enter password:

New password:

Confirm new password:

4. Modify the data storage directory

Installed by default in the / var/lib/mysql/ directory

Modify to the self-defined directory / etedata

Do the following:

Root@ e2emydb02[192.168.199.4] [/ root] # service mysql stop

Root@ e2emydb02 [192.168.199.4] [/ root] # cp-R / var/lib/mysql / etedata

Back up the original directory

Root@ e2emydb02[192.168.199.4] [/ root] # mv / var/lib/mysql / var/lib/mysql.bak

Give weight to the / etedata directory

Root@ e2emydb02 [192.168.199.4] [/ root] # chmod 777-R / etedata # # otherwise the startup will report an error: Starting MySQL...The server quit without updating PID file [FAILED] a/mysql/e2emydb01.pid.

Error log: / etedata/mysql/e2emydb02.err

Modify / etc/my.cnf

Root@ e2emydb02[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 the service

Root@ e2emydb02[192.168.199.4] [/ root] # service mysql stop

Shutting down MySQL.... [OK]

Root@ e2emydb02[192.168.199.4] [/ root] # service mysql start

Starting MySQL. [OK]

Root@ e2emydb02 [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)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql >

5. Set server, client and result set to 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 > show variables like'% char%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

8 rows in set (0.00 sec)

Mysql > create database etedb

Query OK, 1 row affected (0.02 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | etedb |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > quit

The second configuration is from Amure-> B

At this point, the basic work has been done.

Configure Amure-> B below

1. Operate on host A:

Root@ e2emydb01[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

The server needs to be restarted after completion.

Root@ e2emydb01[192.168.199.2] [/ root] # service mysql stop

Shutting down MySQL.... [OK]

Root@ e2emydb01[192.168.199.2] [/ root] # service mysql start

Starting MySQL. [OK]

Root@ e2emydb01 [192.168.199.2] [/ root] # mysql-u root-p

Enter password:

2. Add users

Authorized for users, only 192.168.199.4 can be accessed

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' *

The implementation is as follows:

Root@ e2emydb01 [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)

Test on host B:

Root@ e2emydb02 [192.168.199.4] [/ root] # mysql-h292.168.199.2-urepl_user-paired pictures *

Warning: Using a password on the command line interface can be 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)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql >

3. Get the initial status of the server

Execute the lock table on the A server (under the MYSQL command line):

Mysql > FLUSH TABLES WITH READ LOCK

Query OK, 0 rows affected (0.00 sec)

Perform the export initial state on host A:

Root@ e2emydb01 [192.168.199.2] [/ root] # mysqldump-- master-data-uroot-p etedb > etedb.sql

Enter password:

And transfer the file to the B server:

Root@ e2emydb01[192.168.199.2] [/ root] # scp etedb.sql ete@192.168.199.4:/home/ete

Ete@192.168.199.4's password:

Then look at the initial 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 standard red will be used on server B.

4. Execute on server B:

Root@ e2emydb02[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

Root@ e2emydb02[192.168.199.4] [/ root] # service mysql stop

Shutting down MySQL.... [OK]

Root@ e2emydb02[192.168.199.4] [/ root] # service mysql start

Starting MySQL. [OK]

Root@ e2emydb02 [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 primary 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 represent OK only if they are Yes

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 has 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 was just synchronized from server A:

Root@ e2emydb02[192.168.199.4] [/ home/ete] # cp etedb.sql / rootroot@ e2emydb02[192.168.199.4] [/ home/ete] # cd

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.