Linux Learning Notes-28th Lesson-mysql master-slave copy, read/write separation configuration

MySQL Master-slave configuration

Configure to prepare the database to be configured for master-slave synchronization, the master-slave server must have the same database

First, configure the MySQL master server

[[email protected] ~]# vim /etc/my.cnf....................................log-bin= mysql-bin     <==  Open Log Format ....................................server-id=1            <==  the master-slave mark ............ ........ Optional add configuration, filter library binlog-do-db=db1,db2  #需要同步的库binlog-ignore-db=db1,db2  #忽略不同步的库 ................... [[email protected] backup]# mysql -uroot -penter password:welcome to  The mysql monitor.  commands end with ; or \g.your mysql  connection id is 1Server version: 5.5.42-log MySQL Community  server  (GPL) copyright  (c)  2000, 2015, oracle and/or its affiliates.  all rights reserved. oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their  Respectiveowners. type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> grant replication slave on *.* to  ' repl ' @ ' 192.168.1.13 '  identified by  ' 123 '; query ok, 0 rows affected  (0.00 sec) mysql>mysql> flush  privileges; query ok, 0 rows affected  (0.00 sec) Mysql> flush tables with  read lock; query ok, 0 rows affected  (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+| file              | position | binlog_do_db | binlog_ ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000014 |      332  |              |                   |+------------------ +----------+--------------+------------------+

Second, configure MySQL from the server

#  Modify the configuration file from MySQL [[email protected] ~]# vim /etc/my.cnf ... ...].............. server-id=2      modifies the ID number to differentiate between the master and slave server identities ......... ........ [[Email protected] tmp]# mysql -uroot -penter password:welcome to the  MySQL monitor.  Commands end with ; or \g.Your MySQL  Connection id is 8server version: 5.5.43-log mysql community server   (GPL) copyright  (c)  2000, 2015, oracle and/or its affiliates. all  rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input statement.mysql> slave stop; query ok, 0 rows affected, 1 warning  (0.00 sec) Mysql> change  master to master_host= ' 192.168.1.11 ',  master_port=3306, master_user= ' Repl ',  Master_password= ' 123 ',  master_log_file= ' mysql-bin.000014 ', master_log_pos=332; query ok, 0 rows affected  (0.08 sec) mysql> slave start; query ok, 0 rows affected  (0.01 sec) mysql> show slave status\g ;*************************** 1. row ***************************                slave_io_state: waiting for master  to send event                   master_host: 192.168.1.11        & nbsp;         master_user: repl                   Master_Port: 3306                 Connect_Retry:  60              master_log_file:  mysql-bin.000014          read_master_log_pos: 332                relay_log_file:  slave-relay-bin.000003                 relay_log_pos: 253        relay_master_log_file : mysql-bin.000014        #  This part of the tip for,yes              slave_io_running: yes             Slave_SQL_Running: 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: 332               Relay_Log_Space: 409               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#  This section does not have error hints,ok                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                last_sql_error:  replicate_ Ignore_server_ids:             master_server_ id: 11 row in set  (0.00 sec) error:no query specified

The next step is to test the master server.

This article is from the "topspeed_king" blog, make sure to keep this source http://mylinuxlife.blog.51cto.com/4706737/1656898

Linux Learning Notes-28th Lesson-mysql master-slave copy, read/write separation configuration