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

Set up master-slave replication and read-write separation of mysql +-+ (write) +-+ | client | +-+ | master | +-+ | | +-+ | +-+ (read) | (read) + -+ | amoeba | +-|-+ (write) | +-+ | | (read) | | +-+ | | +-+ | client | +-| slave1 | +-+- -+ | slave2 | +-+ +-+ (replication) +-+ master mysql:172.17.0.4slave1 mysql:172.17.0.5slave2 mysql:172.17.0.6mysql-proxy (amoeba): 172.17.0.8Test host:172.17.0.7 master-slave replication: master mysql: install mysqlserver and mysqlyum install Mysql-server mysql- y modify the configuration file vim / etc/my.cnf.d/ server.cnf [mysqld] server-id = 1log-bin = master-bin start mysql/etc/init.d/mysql start to change root password mysqladmin-uroot-p password mysql login mysql test and check master status mysql- uroot-pmysqlWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 22449Server version: 5.5.47-MariaDB-log MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql-bin.000001 | 3648 | +- + 1 row in set (0.03 sec) create master-slave synchronization account MariaDB [(none)] > grant replication slave on *. * to 'mysqlmaster'@'172.17.0.%' identified by' 123456' Slave1 mysql: install mysqlserver and mysqlyum install mysql-server mysql- y modify configuration file vim / etc/my.cnf.d/ server.cnf [mysqld] server-id = 2 # id must be unique log-bin = slave-bin start mysql/etc/init.d/mysql start modify root password mysqladmin-uroot-p password mysql create synchronization file MariaDB [(none)] > change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456' Master_log_file='mysql-bin.000001',master_log_pos=3648 Check whether it is successful by MariaDB [(none)] > start slave;. Make sure that the following two items are YesMariaDB [(none)] > show slave status\ YesSlave_SQL_Running: Yesmaster mysql: create data on master mysql to check whether slave1 mysql is synchronized with MariaDB [(none)] > create database new1;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > create database new2;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | new1 | | new2 | | performance_schema | | test | +-+ 6 rows in set (0. 00 sec) slave1 mysql:MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | new1 | | new2 | | performance_schema | | test | +-+ 6 rows in set (0. 00 sec) We encounter this situation in our production environment: backing up host data Or add a host. Carry on the dual-host structure. The other thing is to add more slaves halfway online. We know that when setting up slave from the machine, you need to specify master_log_file and master_log_pos, that is, specify the binlog file and the offset value. That is to say, the slave can synchronize data from binlog files in any location. For example, if we back up the binlog file somewhere else and put it somewhere else, one day there is something wrong with the database and we need to recover some data from the file. To add a new slave, there are two ways: copy from the master machine; the other is to copy directly from the slave. MySQL-add a new slave from the master-slave structure 1.copy mastermaster mysql: lock the database MariaDB [(none)] > flush tables with read lock;Query OK, 0 rows affected (0.04 sec) to check the host status, and click the file position parameter MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+ | mysql-bin.000001 | 3974 | +- + backup all databases mysqldump-- all-databases-uroot-pmysql > backup.sql to the newly added slave host scp backup.sql root@172.17.0.6:/rootslave2 mysql: install mysqlserver and mysqlyum install mysql-server mysql- y modify the configuration file vim / etc/my.cnf.d/ server.cnf [mysqld] server-id = 3 # id must be unique log-bin = slave2-bin start mysql/etc/init.d/mysql start modify root The password mysqladmin-uroot-p password mysql is imported into the database mysql-uroot-pmysql from the master server scp.

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.