mysql-5.7 Master-Slave configuration

MySQL Master-slave configuration

Download the latest MySQL yum source

1, wget https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm

Install the latest MySQL

RPM-IVH mysql57-community-release-el6--y install Mysql-server  

Start the MySQL database

Service mysqld Start

Tip: Because 5.7 initialization automatically generates a password:

CAT/var/log/Mysqld.log2018-Geneva-24T13: +: -.925206Z0[Warning] TIMESTAMP withImplicitDEFAULT value isDeprecated. Please use--explicit_defaults_for_timestamp
Server option (see documentation forMore details).2018-Geneva-24T13: +: Wu.106661Z0[Warning] Innodb:new log files created, lsn=457902018-Geneva-24T13: +: Wu.588874Z0[Warning] innodb:creating FOREIGN KEY constraint system tables.2018-Geneva-24T13: +: Wu.805786Z0[Warning] No existing UUID has been found, so we assume that This  isThe first time that ThisServer has been
Started. Generating aNewuuid:0abbeb4b-2f66-11e8-81f3-000c292c7cea.2018-Geneva-24T13: +: Wu.810834Z0[Warning] Gtid table isNot ready to be used. Table'mysql.gtid_executed'cannot be opened.2018-Geneva-24T13: +: Wu.841042Z1[Note] A Temporary password isGenerated for [email protected]:: Kufy//k0zk& (This is a randomly generated password) 

Log in to the MySQL database and change the password:

MySQL    -uroot  -'root'@'localhost'  [email protected]'

1. Configure the master configuration file my.cnf

#打开日志 (host needs to open) log-bin=mysql-bin# #服务器id (This is the only) server-id=1#给从机同步的库 (can write multiple libraries) Binlog -do-db=mydbbinlog-does-db=mydb2binlog-do-db=test# Automatic cleanup of log files 5 days ago Expire_logs_days=5

2. Modify the SLAVE/ETC/MY.CNF configuration from the server from the database

#服务器idserver-id=2# #要从主机同步的库replicate-do-db=mydbreplicate-Do -db=mydb2replicate-do-db=test

3. After the modification, restart the MySQL master database and MySQL service from the database

Service mysqld Restart

4. Configure the primary database for the master server

master database Licensing Sync Account

' Root '@'192.168.10.115'[email protected]'  0 1 Warning (0.01 sec)

Refresh Permissions

FLUSH privileges;

View Primary Service Status

Mysql> Show Master Status, +------------------+----------+-----------------+------------------+---------------- ---+| File             | Position | binlog_do_db    | binlog_ignore_db | Executed_gtid_set |+------------------+----------+-----------------+------------------+-------------------+| mysql-bin.000001 |      602 | mydb,mydb2,test |                  |                   | +------------------+----------+-----------------+------------------+-------------------+1 row in Set (0.00 sec)

5. Configure the slave database from the server

mysql> change MASTER to master_host= ' 192.168.10.116 ',    master_user= ' root ',    master_password= ' [ Email protected] ',    master_log_file= ' mysql-bin.000001 ', \ (this is based on the results of the main server query),    master_log_pos=602; (That is, the master server Show Master status;) Query OK, 0 rows affected, 2 warnings (0.13 sec)

Turn on slave sync

Start slave;

View the slave status

show slave status \g;

Mysql> Show slave status \g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.10.116 master_user:root master_port:3306 connect_retry:60 master_log_file:mysql-bin.000001 read_master_log_pos:602 Relay_log_file:localhost-re lay-bin.000002 relay_log_pos:320 relay_master_log_file:mysql-bin.000001 Slave_io_runni           Ng:yes (This position shows Yes) Slave_sql_running:yes (this location also shows yes for the master-slave replication configuration) replicate_do_db:mydb,mydb2,test Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table : replicate_wild_ignore_table:last_errno:0 Last_error:skip_coun          ter:0 exec_master_log_pos:602 relay_log_space:531    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:0master_ssl_verify_server_cert:no last_io_e rrno:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_se             Rver_ids:master_server_id:1 Master_uuid:0abbeb4b-2f66-11e8-81f3-000c292c7cea Master_info_file:/var/lib/mysql/master.info sql_delay:0 sql_remaining_delay:null Slave _sql_running_state:slave have read all relay log;      Waiting for more updates 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 replicate_rewrite_db: Channel_name:master_tls_version:1 row in Set (0.00 sec)

When both slave_io_running and slave_sql_running are yes, the master-slave replication succeeds

6. Stop slave Sync

Stop slave;

7. Revoke permissions that have been assigned to the MySQL sync account

Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":

GRANT REPLICATION SLAVE on * * to ' root ' @ ' 192.168.10.116 ' identified by ' XXXXXX ';

REVOKE REPLICATION SLAVE on * * from ' root ' @ ' 192.168.10.116 ';

8. Authorized account can log in remotely

GRANT all on * * to username @ '% ' identified by ' password ' with GRANT OPTION;

mysql-5.7 Master-Slave configuration