Following up the following article: Backup database for introduction to MySQL database
Installation environment instructions
System environment:
[root@~]# cat /etc/redhat-release CentOS release 6.5 (Final) [root@~]# uname -r 2.6.32-431.el6.x86_64
Database:
Because it is a simulated environment, the master and slave libraries are on the same server, and the IP address of the server is 192.168.1.7.
- The main library uses port 3306
- Use 3307 port from library
- Database data directory / data
Install MySQL database service
Download packages
Today we deploy MySQL database services with binary installation packages. For other methods of installation and deployment, please refer to the previous article.
[root@~]#wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51-linux2.6-x86_64.tar.gz
Create Data Directory, Software Installation Directory
[root@~]#mkdir /data｛3306，3307｝ -p [root@~]#mkdri /application
Decompression software
[root@~]#tar zxf mysql-5.5.51-linux2.6-x86_64.tar.gz [root@~]#mv mysql-5.5.51-linux2.6-x86_64 /application/mysql-5.5.51 [root@~]#ln -s /application/mysql-5.5.51 /application/mysql
Create user
[root@~]#groupadd mysql [root@~]#useradd -g mysql -M mysql
Initialize the database
[root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql [root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
create profile
[root@~]#vi /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k thread_stack = 192K tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin #The key point of master-slave synchronization is that slave libraries need not be opened relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 #The master and slave library ID s are not identical [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql3306.err pid-file=/data/3306/mysqld.pid
Database startup script:
[root@~]#vi /data/3306/mysql #!/bin/sh port=3306 user="root" pwd="123456" Path="/application/mysql/bin" sock="/data/${port}/mysql.sock" start_mysql() { if [ ! -e "$sock" ];then printf "Starting MySQL...\n" /bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running...\n" exit fi } stop_mysql() { if [ ! -e "$sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown fi } restart_mysql() { printf "Restarting MySQL...\n" stop_mysql sleep 2 start_mysql } case $1 in start) start_mysql ;; stop) stop_mysql ;; restart) restart_mysql ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
Note: The master-slave library configuration file is the same as the startup file, only need to modify the port and server-id to complete the configuration.
Authorize directories and increase executable privileges for startup files
[root@~]#chown -R mysql.mysql /data [root@~]#find /data -name mysql -exex chmod +x {} \;
Start the database
[root@~]#/data/3306/mysql start [root@~]#/data/3307/mysql start
Modify the default database password
[root@~]#mysqladmin -uroot password '123456' -S /data/3306/mysql.sock [root@~]#mysqladmin -uroot password '123456' -S /data/3307/mysql.sock
Test login, you can login to two databases to complete the installation process
Configure main library
1) Backup the main library
mkdir /backup
Login to the main library to create a synchronous user and authorize
[root@~]#mysql -uroot -p123456 -S /data/3306/mysql.sock mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by'123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
Execute lock table operation
[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush table with read lock;"
Backup main library
[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/backup/mysql.log [root@~]#/application/mysql/bin/mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/backup/mysql.sql.gz
Unlock table status
[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;"
Note: The above operations can also be done by landing in the main database, but it should be noted that after the lock table operation, another window should be opened for data backup, which can not be withdrawn directly to prevent incomplete data backup caused by data writing. It is best to use non-interactive operations.
Configuring slave libraries to achieve master-slave synchronization
Unzip the backup files of the main library and restore the database
[root@backup ]#gzip -d mysql.sql.gz [root@backup ]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3307/mysql.sock < mysql.sql
View LOG logs
[root@backup ]#cat mysql.log +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 424 | | | +------------------+----------+--------------+------------------+
Log in from the library to perform the following actions
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.1.7', #Server IP -> MASTER_PORT=3306, #Main library port -> MASTER_USER='rep', #Synchronized users -> MASTER_PASSWORD='123456', #Synchronized user passwords -> MASTER_LOG_FILE=' mysql-bin.000002', #binlog file -> MASTER_LOG_POS=424; #Location point mysql> start slave; #Open synchronization
Wait for 60S to see synchronization status
[root@backup ]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running" Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
As long as this happens, master-slave synchronization is successful.
Test master-slave synchronization
Create a database in the main library
[root@backup ~]# mysql -S /data/3306/mysql.sock -e "create database tongbuku" [root@backup ~]# mysql -S /data/3306/mysql.sock -e "show databases" +-----------------------------+ | Database | +-----------------------------+ | information_schema | | mysql | | performance_schema | | test | | tongbuku | +-----------------------------+
View slave library synchronization
[root@backup ~]# mysql -S /data/3307/mysql.sock -e "show databases" +-----------------------------+ | Database | +-----------------------------+ | information_schema | | mysql | | performance_schema | | test | | tongbuku | +-----------------------------+
Indicates that the master-slave synchronization state is normal, or you can create tables in the new data tables of the master database and insert new data to test the master-slave synchronization state.
For more articles on Mysql technology, please keep an eye on Migrant Workers'Technological Path. If you need to pay attention to more articles in other technical directions, you can also pay attention to Migrant Worker's personal Wechat Public Number: Migrant Worker's Technological Road, and the answer keywords of the Wechat Public Number Dialogue Box: 1024 can get a latest technical dry goods: including system operation and maintenance, database, redis, MogoDB, e-book, Java basic courses, Java actual combat projects. Architect Comprehensive Course, Architect Practical Project, Big Data, Docker Container, ELK Stack, Machine Learning, BAT Interview Video, etc.