linux--about MySQL in more detail, the master-slave configuration of the database, automatic backup and the use of read and write separation

Source: Internet
Author: User

Tags: enterprise creat operation record column SHA can nlog blog modification

1. Master-Slave Introduction
Data in the enterprise is a very important part of the database to store data many multi-use samples, but there is a hidden danger
• Database Data loss
• A database cannot guarantee quality of service when the number of database accesses is large
2. The role of master and slave
• Real-time disaster preparedness for failover
• Read and write separation, providing query services
• Backup to avoid impacting your business
3. Master-Slave form
• One master and one bundle
• Primary master replication
• One master multi-slave
• Multi-Master One bundle
• Joint level replication

Master-slave replication principle

master-slave replication steps

主库将所有写的操作记录到binlog日志(二进制日志)中并生成一个log dump线程,将binlog日志传给从库的I/O线程
从库生成两个线程,一个I/O线程,一个SQL线程·I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志)文件中·SQl线程,会读取relay log 文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的

Master-Slave Replication configuration steps:
1. Ensure that the database is the same as the data in the primary database
2. Create a sync account in the main database to be licensed to use from the database
3. Configure the primary database (modify the configuration file)
4. Configuration from the database (modify the configuration file)

Configuration requirements:
Environment: Set up two MySQL servers, one as the primary server, one as a slave server, the master server for the write operation from the server to read operations

Configuration information
Primary database ip192.168.56.11 CENTOS7 has data
No data from database ip192.168.56.138 CENTOS7

Master-Slave synchronization settings
Firewall settings

[[email protected] ~]# systemctl stop firewalld[[email protected] ~]# systemctl disable firewalld[[email protected] ~]# sed -ri ‘s/(SELINUX=).*/\1disabled/g‘ /etc/selinux/config[[email protected] ~]# setenforce 0

Download MySQL package in binary format

[[email protected] ~]# cd /usr/src/[[email protected] src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz[[email protected] src]# lsdebug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

Create and group

[[email protected] src]# groupadd -r mysql[[email protected] src]# useradd -M -s /sbin/nologin -g mysql mysql[[email protected] src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[[email protected] src]# cd /usr/local/[[email protected] local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"

Modify the genus/usr/local/mysql of the directory

[[email protected] local]# chown -R mysql.mysql /usr/local/mysql[[email protected] local]# ll /usr/local/mysql -dlrwxrwxrwx. 1 mysql mysql 36 9月   7 11:43 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

Adding environment variables

[[email protected] local]# ls /usr/local/mysqlbin  COPYING  docs  include  lib  man  README  share  support-files[[email protected] local]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh[[email protected] local]# . /etc/profile.d/mysql.sh[[email protected] local]# echo $PATH/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

Set up a data storage directory

[[email protected] local]# mkdir /opt/data[[email protected] local]# chown -R mysql.mysql /opt/data/[[email protected] local]# ll /opt/总用量 0drwxr-xr-x. 2 mysql mysql 6 9月   7 11:47 datadrwxr-xr-x. 2 root  root  6 3月  26 2015 rh

Initializing the database

[[email protected] local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/2018-09-07T03:48:13.790964Z 1 [Note] A temporary password is generated for [email protected]: n*Fsa&a+O1kN  /最后会生成一个临时密码

From: Database initialization, same as previous steps, so omit

[[email protected] local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/2018-09-07T06:25:11.123940Z 1 [Note] A temporary password is generated for [email protected]: QR(XhNPpk6Qi

Build configuration File/

[[email protected] ~]# cat > /etc/my.cnf <<EOF[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveEOF
//配置服务启动脚本[[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g‘ /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(datadir=).*#\1/opt/data#g‘ /etc/init.d/mysqld

Start MySQL

[[email protected] ~]# /etc/init.d/mysqld start[[email protected] ~]# ps -ef|grep mysql[[email protected] ~]# ss -antlLISTEN 0 80 :::3306 :::*

Change Password
Login with temporary password

[[email protected]st ~]# /usr/local/mysql/bin/mysql -uroot -pEnter password:mysql>//设置新密码mysql> set password = password(‘guohui123‘);以上是两台数据库服务器的配置,配置方式相同

Master://view backup of master and slave libraries

[[email protected] local]# mysql-uroot-pguohui123-e ' show databases; ' MySQL: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS | Create a library, table mysql> use guohuidatabase changedmysql> CREATE TABLE student (ID int (one) not null,name VA Rchar (+) not null,age TINYINT (3)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT into student (id,name,age) value (1, ' Tom ', 20); Query OK, 1 row affected (0.05 sec) mysql> desc student;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| ID | Int (11) |     NO | |       NULL | || name | varchar (100) |     NO | |       NULL | || Age | tinyint (3) |     YES | |       NULL | |+-------+--------------+------+-----+---------+-------+3 rows in Set (0.01 sec) mysql> Select * FROM student;+----+------+------+| ID | name |  Age |+----+------+------+| 1 |   Tom | |+----+------+------+1 row in Set (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | Guohui | | MySQL | | Performance_schema | | SYS |+--------------------+5 rows in Set (0.00 sec)

Master://Full backup Main Library
The full master library needs to open a separate terminal, to add a read lock to the data, to avoid other people in the backup during the write data caused inconsistent data, and the lock must be completed after the backup to exit, Exit is unlocked

mysql> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.01 sec)

Master://Back up the main library and transfer the files to the slave library

[[email protected] ~]# mysqldump -uroot -pguohui123 --all-databases > /opt/all-20180907-1.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# ls /optall-20180907-1.sql  data  rh//传输[[email protected] ~]# scp /opt/all-20180907-1.sql [email protected]:/opt/The authenticity of host ‘192.168.56.138 (192.168.56.138)‘ can‘t be established.ECDSA key fingerprint is SHA256:byE1DjsPUpqjJssSP+RcbkM7+wcjdzMkjW3u09KrSB8.ECDSA key fingerprint is MD5:c6:d2:a1:34:73:2f:66:c9:ac:db:ca:2b:7a:78:4f:75.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added ‘192.168.56.138‘ (ECDSA) to the list of known hosts.[email protected]‘s password:1    //这里输入root密码all-20180907-1.sql                                                  100%  783KB  19.2MB/s   00:00  主://接触锁表状态mysql> quitBye

From://Restore a backup of the main library from the library and see which libraries are available to ensure consistency with the main library

//查看主备传过来的文件[[email protected] ~]# ls /optall-20180907-1.sql  data  rh将文件导入从数据库[[email protected] ~]# mysql -uroot -pguohui123 < /opt/all-20180907-1.sqlmysql: [Warning] Using a password on the command line interface can be insecure.//查看数据库[[email protected] ~]# mysql -uroot -pguohui123 -e ‘show databases;‘mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || guohui             || mysql              || performance_schema || sys                |+--------------------+

Master://Create a sync account in the main database to be licensed to use from the database

创建数据库用户guohui,密码:guohui123,只能在192.168.56.138上远程登陆mysql> CREATE USER ‘guohui‘@‘192.168.56.138‘ IDENTIFIED BY ‘guohui123‘;Query OK, 0 rows affected (0.00 sec)赋予权限用户guohui,可以在192.168.56.138上访问本机所有数据库的所有表,无需密码:mysql> GRANT REPLICATION SLAVE ON *.* TO ‘guohui‘@‘192.168.56.138‘;Query OK, 0 rows affected (0.00 sec)重新加载特权:mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql>

Master://Configure the primary database

[[email protected] ~]# vim/etc/my.cnf[mysqld]basedir =/usr/local/mysqldatadir =/opt/datasocket =/tmp/ Mysql.sockport = 3306pid-file =/opt/data/mysql.pidlog-error =/opt/data/mysqld.log//error log user = Mysqlskip-name-resolve #log-bin = mysql_bin//start binlog log Server-id = 2//database server Unique identifier, the Server-id value of the main library must be larger than the boot from the library [[email protected] ~]#/etc/in It.d/mysqld restarterror! MySQL server PID file could not being found! Starting mysql.logging to '/opt/data/mysqld.log '. success! View Main Library status:mysql> show Master status;mysql_bin, binary file written in file name 000001 version position location 154+------------------+------ ----+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+|      mysql_bin.000001 |              154 |                  |                   | |+------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec) 

From://configuration from Database

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.pidlog-error = /opt/data/mysql.loguser = mysqlskip-name-resolve#replicationserver-id = 5  //设置从库的唯一标识符,从库的server-id值必须小于主库的该值relay-log = mysql_relay_log    //启动中继日志relay-log启动[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL.Logging to ‘/opt/data/mysql.log‘.SUCCESS!

From://Configure and start master-slave replication

CHANGE MASTER TO                       指定的主服务器MASTER_HOST=‘192.168.56.11‘,     指定的主服务器ipMASTER_USER=‘guohui‘,MASTER_PASSWORD=‘guohui123‘,  密码MASTER_LOG_FILE=‘mysql-bin.000001‘,     二进制文件的名字与版本MASTER_LOG_POS=154;                   位置 154mysql> change master to master_host=‘192.168.56.11‘,master_user=‘guohui‘,master_password=‘guohui123‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.02 sec)启动从服务的从服务mysql> start slave;Query OK, 0 rows affected (0.00 sec)

From://view from server status

mysql> show slave status \G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.56.11                  Master_User: guohui                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000002          Read_Master_Log_Pos: 154               Relay_Log_File: mysql_relay_log.000004                Relay_Log_Pos: 367        Relay_Master_Log_File: mysql_bin.000002             Slave_IO_Running: Yes       //必须yes            Slave_SQL_Running: Yes     //必须yesmysql>

Test:
Main://Insert data into the database

mysql> select * from student;+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   20 |+----+------+------+1 row in set (0.00 sec)mysql> insert into student value (1,‘jerry‘,23);Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | tom   |   20 ||  1 | jerry |   23 |+----+-------+------+2 rows in set (0.00 sec)mysql>

From://View Test results

mysql> use guohui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from student;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | tom   |   20 ||  1 | jerry |   23 |+----+-------+------+2 rows in set (0.00 sec)mysql>

This shows that the database of the master-slave function, build success

linux--about MySQL in more detail, the master-slave configuration of the database, automatic backup and the use of read and write separation

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: