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

This article mainly introduces "how to install Mysql dual hot backup". In daily operation, I believe many people have doubts about how to install Mysql dual hot standby. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to install Mysql dual hot backup". Next, please follow the editor to study!

Install mysql#tar-xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar # yum localinstall * .rpm1.1 modify mysql configuration # For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/ TMP [client] default-character-set=ut f8mb4 [mysqld] character_set_server=utf8mb41.2 permission modification [root@172 ~] # Chown-R mysql:mysql / data [root@172 ~] # chmod 777-R / data/ [root@172 ~] # chmod-R 777 / tmp1.3 start the mysql service [root@172 ~] # service mysqld restartStopping mysqld: [FAILED] Initializing MySQL database: [OK] Installing validate password plugin: [OK] Starting mysqld: [OK] 1.4 View temp password more / var/log/mysqld.log | grep temporary1.5 modifies root password db1ALTER USER 'root'@'localhost' IDENTIFIED BY' *' Flush privileges;exit;db2ALTER USER 'root'@'localhost' IDENTIFIED BY' *'; flush privileges;exit; II. Configure master-slave synchronization master1172.28.8.187master2172.28.8.1882.1 configure the password that master1 gives master2 login

Master1

Create user 'repl' identified by' *'; GRANT REPLICATION SLAVE ON *. * TO 'repl'@'172.28.8.188' IDENTIFIED BY' *'; FLUSH PRIVILEGES;mysql > create database mydb default charset utf8 Test whether repuser can log in to the database mysql-urepl-p-h272.28.8.1872.1.1 Master1 configuration my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL on 172.28.8.188. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmpcharacter_set_server=utf8mb4server-id=177log-bin=/var/log/mysql/mysql-bin.logread-only=0binlog-ignore-db=mysqlbinlog-ignore- Db=information_schemaexpire_logs_days= 365auto-increment-increment = 2auto-increment-offset = 1 [client] default-character-set=utf8mb42.2 Master2 configuration my.cnf# except server-id Others are consistent with master1 2.2.1 Master2 creates an account password for Master1 and authorizes create user 'repl' identified by' *' GRANT REPLICATION SLAVE ON *. * TO 'repl'@'172.28.8.187' IDENTIFIED BY' *'; FLUSH PRIVILEGES;2.3 to view Master synchronization status

Master1

Mysql > show master status +-- +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed _ Gtid_Set | +-+-+ | mysql-bin.000001 | 154,154 | mydb | mysql Information_schema | | +-- +-+ 1 row in set (0.00 sec)

Master2

Mysql > show master status +-- +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed _ Gtid_Set | +-+-+ | mysql-bin.000001 | 154,154 | mydb | mysql Information_schema | | +-- +-+ 1 row in set (0.00 sec)

Set master1 to synchronize from master2

Mysql > CHANGE MASTER TO MASTER_HOST='172.28.8.188',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='b4l:GGtG3s0*',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=860;mysql > SHOW SLAVE STATUS\ Gmysql > START SLAVE;mysql > SHOW SLAVE STATUS\ G

Set master2 to synchronize from master1

Mysql > CHANGE MASTER TO MASTER_HOST='172.28.8.187',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='2S1*8pr+ BzqH ^ 8T`, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1497;mysql > SHOW SLAVE STATUS\ Gmysql > START SLAVE;mysql > SHOW SLAVE STATUS\ G

If the following two items appear, the configuration is successful!

Slave_IO_Running: Yes Slave_SQL_Running: Yes3. Two-master synchronous test

Access to master1 mysql database

Mysql > create database crm;Query OK, 1 row affected (0.00 sec) mysql > use crm;Database changedmysql > create table employee (id int auto_increment,name varchar (10), primary key (id)); Query OK, 0 rows affected (0.00 sec) mysql > insert into employee (name) values ('a'); Query OK, 1 row affected (0.00 sec) mysql > insert into employee (name) values ('b'); Query OK, 1 row affected (0.00 sec) mysql > insert into employee (name) values ('c') Query OK, 1 row affected (0.06 sec) mysql > select * from employee;+----+-+ | id | name | +-- +-+ | 1 | a | 3 | b | | 5 | c | +-- +-- + 3 rows in set (0.00 sec)

Go to master2 and see if you have the database crm and the employee table.

Mysql > show databases;+-+ | Database | +-+ | information_schema | | crm | | mysql | | performance_schema | +-+ 4 rows in set (0.00 sec) mysql > use crm Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables;+-+ | Tables_in_crm | +-+ | employee | +-+ 1 row in set (0.00 sec) mysql > select * from employee +-+ | id | name | +-+-+ | 1 | a | 3 | b | 5 | c | +-+ 3 rows in set (0.00 sec) mysql > insert into employee (name) values ('d'); Query OK, 1 row affected (0.00 sec) mysql > select * from employee +-+-- +-+ | id | name | +-+-+ | 1 | a | 3 | b | 5 | c | | 7 | d | +-- +-- + 4 rows in set (0.00 sec)

Check in master1 to see if there is any data that has just been inserted in master2.

Mysql > select * from employee;+----+-+ | id | name | 1 | a | 3 | b | | 5 | c | 7 | d | +-- +-+ 4 rows in set (0.00 sec). This is the end of the study on "how to install Mysql dual hot backup". I hope you can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.