Shulou(Shulou.com)06/03 Report--

Three servers: host 192.168.11.126192.168.11.127, from: 192.168.11.128

Libraries ready for synchronization: 192.168.11.126, account_db,192.168.11.127,game_db

First, install mysql on the respective servers, and install 2 databases with mysqld_multi on the slave machine, 3307pint 3308

3307 corresponds to 192.168.11.126 and 3308 corresponds to 192.168.11.127

1. Set up a directory:

Mkdir-p / data/mysql {3307 pm 3308}

Mkdir-p / data/mysql {3307 recorder 3308} / data

Mkdir-p / data/mysql {3307 recorder 3308} / binlog

Mkdir-p / data/mysql {3307 recorder 3308} / relay_log

Chown-R mysql:mysql / data/mysql {3307 pm 3308}

Chown-R mysql:mysql / data/mysql {3307 recorder 3308} / data

Vim / etc/my.cnf

Add:

[mysqld_multi]

Mysqld=/data/mysql/bin/mysqld_safe

Mysqladmin=/data/mysql/bin/mysqladmin

Log=/data/mysql/mydata/log/mysqld_multi.log

[mysqld1]

Port= 3307

Socket= / data/mysql3307/mysql.sock

Datadir = / data/mysql3307/data

Server-id = 1231

Expire_logs_days = 2

Log-bin = / data/mysql3307/mysqllog/binlog/mysql-bin

Replicate-do-db=account_db

Replicate-ignore-db=mysql

Relay_log = / data/mysql3307/relay_log/mysql-relay-bin

Log_slave_updates = 1

Character_set_server = utf8

Sql_mode = NO_AUTO_CREATE_USER

Read_only = 0

Wait_timeout = 64800

Interactive_timeout = 64800

Skip-name-resolve

# default-character-set = utf8

Lower_case_table_names = 1

Initialize the database:

/ data/mysql/scripts/mysql_install_db-user=mysql-basedir=/data/mysql-datadir=/data/mysql3307/data

-- if user-basedir is separated, it will make a mistake.

The following message pops up:

/ data/mysql/scripts/mysql_install_db-user=mysql-basedir=/data/mysql-datadir=/data/mysql3307/data

WARNING: The host 'iZbp11h60sm7xheqt4hlh2Z' could not be looked up with / data/mysql/bin/resolveip.

This probably means that your libc libraries are not 100% compatible

With this binary MySQL version. The MySQL daemon, mysqld, should work

Normally with the exception that host name resolving will not work.

This means that you should use IP addresses instead of hostnames

When specifying MySQL privileges!

Installing MySQL system tables...2017-07-21 16:21:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-07-21 16:21:10 0 [Note] Ignoring-- secure-file-priv value as server is running with-- bootstrap.

2017-07-21 16:21:10 0 [Note] / data/mysql/bin/mysqld (mysqld 5.6.34) starting as process 24280.

2017-07-21 16:21:10 24280 [Note] InnoDB: Using atomics to ref count buffer pool pages

2017-07-21 16:21:10 24280 [Note] InnoDB: The InnoDB memory heap is disabled

2017-07-21 16:21:10 24280 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

.

2017-07-21 16:21:12 24280 [Note] RSA private key file not found: / data/mysql3307/data//private_key.pem. Some authentication plugins will not work.

2017-07-21 16:21:12 24280 [Note] RSA public key file not found: / data/mysql3307/data//public_key.pem. Some authentication plugins will not work.

2017-07-21 16:21:12 24280 [Note] Binlog end

2017-07-21 16:21:12 24280 [Note] InnoDB: FTS optimize thread exiting.

2017-07-21 16:21:12 24280 [Note] InnoDB: Starting shutdown...

2017-07-21 16:21:13 24280 [Note] InnoDB: Shutdown completed; log sequence number 1625977

OK

2017-07-21 16:21:13 24302 [Note] RSA public key file not found: / data/mysql3307/data//public_key.pem. Some authentication plugins will not work.

2017-07-21 16:21:13 24302 [Note] Binlog end

2017-07-21 16:21:13 24302 [Note] InnoDB: FTS optimize thread exiting.

2017-07-21 16:21:13 24302 [Note] InnoDB: Starting shutdown...

2017-07-21 16:21:15 24302 [Note] InnoDB: Shutdown completed; log sequence number 1625987

OK

To start mysqld at boot time you have to copy

.

WARNING: Default config file / etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

-- defaults-file argument to mysqld_safe when starting the server

Also prepare the database for port 3308 and initialize and configure the my.cnf port, synchronize the database name modification:

/ data/mysql/scripts/mysql_install_db-user=mysql-basedir=/data/mysql-datadir=/data/mysql3308/data

Start:

Mysqld_multi-- defaults-extra-file=/etc/my.cnf start 1Pol 2

Mysqld_multi-defaults-extra-file=/etc/my.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

Stop:

Mysqld_multi-- defaults-extra-file=/etc/my.cnf stop 1Pol 2

Enter mysql individually:

Mysql-socket=/data/mysql3307/mysql.sock

Mysql-- socket=/data/mysql3307/mysql.sock-uroot-p

Mysql-socket=/data/mysql3308/mysql.sock

[root@iZbp11h60sm7xheqt4hlh2Z data] # mysql-- socket=/data/mysql3308/mysql.sock

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.6.34-log Source distribution

Copyright (c) 2000, 2016, 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 >

Mysql > select @ @ port

+-+

| | @ @ port |

+-+

| | 3308 |

+-+

1 row in set (0.00 sec)

Mysql-- socket=/data/mysql3308/mysql.sock-uroot-p

Use game_db

Source / home/game_db.sql

Select * from user where user = 'root'\ G

Update user set host= "192.168.11.%" where host='127.0.0.1'

Update user set password=password ('123456') where user='root'

The main library gives rep permission:

Grant replication slave,file on *. * to 'replicate'@'192.168.11.128' identified by' rep@123'

Add fields above the main library:

Server-id=1 # sets the server id, and a value of 1 means the primary server. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Log_bin=mysql-bin # starts the MySQ binary logging system. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Binlog-do-db=game_db # requires a synchronized database name. If you have more than one database, you can repeat this parameter, one row for each database

Binlog-ignore-db=mysql # does not synchronize the mysql system database

Service mysqld restart # restart MySQL

Mysql-u root-p # enter the mysql console

Show master status; looks at the master server and the following similar message appears. Take 192.168.11.127game_db as an example:

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000002 | 120 | game_db | mysql | |

+-+

Configure the my.cnf file of MySQL slave server

Vi / etc/my.cnf # edit the configuration file and add the following in the [mysqld] section

There is already a line of server-id=1 in the server-id=2 # configuration file, and its value is modified to 2, indicating that it is from the database.

Log-bin=mysql-bin # starts the MySQ binary logging system. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Replicate-do-db=game_db# requires a synchronized database name. If there are multiple databases, you can repeat this parameter, one row for each database.

Replicate-ignore-db=mysql # does not synchronize the mysql system database

Restart mysql3308 after configuration

Stop slave

Change master to master_host='192.168.11.127',master_user='replicate',master_password='rep@123',master_log_file='mysql-bin.000002',master_log_pos=120

Start slave

Show slave status\ G

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.127

Master_User: replicate

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 2234

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 2069

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: game_db,game_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: 2234

Relay_Log_Space: 2242

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: 84631394-7036-11e7-b3e9-000c29b53e0b

Master_Info_File: / data/mysql3308/data/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)

ERROR:

No query specified

If the note:mysql port is not 3306 by default, it can be modified in my.cnf. Remember to add master_port=port to the slave machine.

Change master to master_host='192.168.11.127',master_user='replicate',master_port=3306,master_password='rep@123',master_log_file='mysql-bin.000002',master_log_pos=120

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.