CentOS MySQL dual-machine Hot Backup configuration and centos dual-machine Hot Backup

CentOS MySQL dual-machine Hot Backup configuration and centos dual-machine Hot Backup
1 Overview

In integration projects, you need to handle installation configurations in different environments. There are roughly three mainstream operating systems: Linux, Windows, and UNIX. There are two main reasons why Linux is favored:

First, Linux as a free software has two features: First, it provides free source code, and second, fans can freely modify, copy, and publish the source code of the program as needed, and published on the Internet. This attracts operating system experts from all over the world to write a variety of drivers and application software for Linux, making Linux not only a kernel, it also includes system management tools, a complete development environment, development tools, and application software.

Secondly, Linux is a UNIX system variant, so it has a series of excellent features of the UNIX system. UNIX applications can be easily transplanted to the Linux platform, this makes Linux easy for UNIX users.

Linux is one of the most common considerations for Linux, such as security, reliability, high efficiency, free of charge, good network support, and full compatibility with UNIX. In this way, you need to install the database in Linux, such as Oracle and MySQL. This article describes how to install the MySQL database in a Linux environment and how to configure the MySQL database. Some common problems are also described, at last, we will show a common MySQL database backup method-Dual-machine hot backup.

2 intended readers

• Shutong changlian New Employee

• Technology enthusiasts

3. Environment Information

Operating System:CentOS 1, 6.4

MySQL:Version: MySQL5.6

4. Glossary

Linux: Linux is a free-to-use and free-propagation Unix-like operating system. It is a POSIX and UNIX-based multi-user, multi-task, multi-thread and multi-CPU operating system. It can run major UNIX tool software, applications and network protocols. It supports 32-bit and 64-bit hardware. Linux inherits the network-centric Unix design philosophy and is a stable multi-user network operating system.

CentOS:(CommunityEnterprise Operating System) is one of the releases of Linux, which is compiled by Red Hat Enterprise Linux according to open source code. Because of the same source code, some servers that require high stability use CentOS instead of the commercial version of Red Hat Enterprise Linux. The difference between the two lies in that CentOS does not contain closed source code software.

MySQL: MySQL is a relational database management system developed by MySQL AB in Sweden. It is currently a product of Oracle. MySQL is the most popular Relational Database Management System. In terms of WEB applications, MySQL is one of the best applications of RDBMS (Relational Database Management System.

MYISAM: One of the two common storage engines of MySQL, the feature is that table data files and table index files are stored independently, suitable for environments with less than 20 million records and a low concurrency, it is suitable for simple queries, does not support transactions, and is prone to data loss, Table Corruption, and frequent repair. The repair often results in data loss. It is suitable for environments with more reads and less writes.

INNODB: One of the two common storage engines of MySQL, the feature is that the table data file and the table index file are the same file, suitable for large-scale data, small-scale data is not efficient, suitable for environments with high concurrency and complex queries, supports transactions, and supports automatic data recovery to minimize data loss

5 procedure 5.1 Installation Method

You can install MySQL on CentOS in two ways: if the network environment is smooth, you can use the YUM library for online installation, another method is to install MySQL by locally uploading MySQL media.

5.1.1 remote yum Installation

• Check whether the built-in MySQL of CentOS is installed

Command:Yum list installed | grep mysql

• First, you must uninstall the dependent files.

Command: for exampleYum-y remove mysql-libs.x86_64

If multiple dependent files exist, they are uninstalled sequentially. When the result is displayed as Complete! Uninstall

• View MySQL version information on the yum Database

Command:Yum list | grep mysqlOrYum-y list mysql *

The CentOS system needs to connect to the network normally.

• Install MySQL through yum

Command:Yum-y installMysql-server mysql-devel

• Conflicting files are displayed during installation, removing conflicting files

Command: for exampleYum-y remove MySQL-client-advanced-5.6.22 *

• Check whether MySQL is installed

Rpm-qa | grep-imysql

-I case-insensitive

• After successful installation, MySQL startup fails. Check the MySQL Log information to locate the cause of the error.

Less/var/log/mysqld. log

• Start MySQL and change the Database Password

Mysql> update user set password = password ('upassword') where user = 'root '; Mysql> flush privileges;

Port Number enabled by the firewall during remote connection

Command:Iptables-I INPUT-p tcp -- dport 3306-j ACCEPT

5.1.2 install local media

• First download the MySQL installation package

Mysql-advanced-5.6.23-linux-glibc2.5-x86_64.tar.gz

• Decompress the package and place it in the installation folder.

MySQL database: MySQL-server-5.6.22-1.linux_glibc2.5.x86_64.rpm

MySQL client: MySQL-client-5.6.22-1.linux_glibc2.5.x86_64.rpm

• Check whether MySQL is installed before installation.

Command: rpm-qa | grep mysql

• If you have installed MySQL before, you need to delete the installed MySQL

Command: for example rpm-e -- nodeps mysql-libs-5.1.47-4.el6.i686

Check whether MySQL is installed again

• Install MySQL Server

Command: rpm-ivh MySQL-server-5.6.22-1.linux_glibc2.5.x86_64.rpm

Preparing... ######################################## ### [100%] 1: mySQL-server ##################################### ###### [100%] ... A random password has been set for the MySQL root USER! You will find that password in '/root/. mysql_secret '. You must change that password on your first connect, No other statement but 'set password' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /Usr/bin/mysql_secure_installation Which will also give you the option of removing the test database. This is stronugly recommended for production servers. See the manual for more instructions. Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web Http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as/usr/my. cnf and Will be used by default by the server when you start it. You may edit this file to change server settings

• Check whether port 3306 of MySQL is enabled.

Command: netstat-nat

No port 3306 is found, indicating that MySQL service has not started

Start MySQL Service

Command: service mysql start

Starting MySQL ...... SUCCESS!

Check the port number 3306 again after the startup is successful.

• Install the client

Command: rpm-ivh MySQL-client-5.6.22-1.linux_glibc2.5.x86_64.rpm

Preparing... ######################################## ### [100%] 1: mySQL-client ^ C ################################### ####### [1, 100%]

5.2 configuration process 5.2.1 backup mode

MySQL supports the following backup methods:

•  Logical backup

Advantages: The biggest benefit is that it can work with the running MySQL automatically. During the running period, it can ensure that the backup is the current point, and it will automatically lock the corresponding operation table, modification by other users is not allowed (only accessible), and modification may be blocked. SQL files can be easily transplanted.

Disadvantages: The backup speed is relatively slow. If there is a large amount of data, it will take a lot of time. If the database server is in the service status provided to the user, during this long operation, it means to lock the table (generally, it is read-locked and only supports reading and writing data ), then the service will be affected.

• Physical backup

Direct Copy is only applicable to tables of the MYISAM type. This type of table is independent from the machine. However, the actual situation is that it is impossible to use all MYISAM tables during database design. It is also impossible: Because the MYISAM table is independent from the machine and can be easily transplanted, this table is selected, which is not the reason for choosing it.

Disadvantages: You cannot operate on the running MySQL Server (during the copy process, users access and update data through applications, so that the current data cannot be backed up) and may not be transplanted to other machines.

In more cases, the table type is selected based on the business characteristics (for example, INNODB must be used to support the transaction mechanism), query speed and service performance.

• Dual-machine Hot Backup

MySQL databases do not have an incremental backup mechanism. Backup is a big problem when the data volume is too large. Fortunately, the MySQL database provides a master-slave backup mechanism (that is, dual-machine Hot Backup)

Advantages:Suitable for large data volumes. Large Internet companies use heat engines to back up MySQL Data. Create multiple database servers for master-slave replication.

Implementation Mechanism:For a MySQL server, there are generally two threads responsible for copying and being copied. After enabling replication:

• As the Master server, each change is recorded in the binary log binlog. (The slave server reads the log and executes it again on its own .)

• As the Slave server Slave, it will log on to the master using the account on the master, read the binlog of the master, and write the Relaylog to its own relay log, then, your SQL thread reads the relay log and executes it again.

When considering dual-machine Hot Standby, you should note that in general, there will be a switching process for dual-machine Hot Standby, which may be about one minute. During the switchover, the Service may be interrupted for a short time. However, after the switchover is complete, the service will be restored normally. Therefore, dual-host Hot Standby is not seamless and uninterrupted, but it can ensure that normal services can be quickly restored in the case of system faults, so that the business will not be affected. If there is no dual-machine hot backup, once a Server failure occurs, the Service may be interrupted for several hours, and the impact on the Service may cause serious losses.

5.2.2 configuration steps

Environment requirements:

1. Master database IP Address: 10.254.1.248

2. Backup database (Slave) IP: 10.254.1.249

3. Ensure data consistency between the two databases before backup

MasterEnd

• Enter the MySQL command line and grant the copy permission to the backup host. That is, the backup host uses the username backup, and the password 123456 is used to connect to the master server. backup is the synchronization account.

Grant file, select, replication slave on *. * to backup@10.254.1.249 identified by '20140901 ';

• Modify the configuration file my. cnf [mysqld]:

Sevice-id = 1 Log-bin = mysql-bin ------------ turn on the log switch, binary log file Max_binlog_size = 104857600 Binlog_format = mixed -------- combination of log mode row level and statement level Binlog-do-db = aeaiesb ------------- operations performed on the aeaiesb database record binary log files, instead of operations performed on the aeaiesb Database Binlog-ignore-db = mysql -------------------- ignore the operation on the mysql database, that is, it is not recorded in the log.

• View master Status

On the MySQL command line:

Show master status Mysql> show master status; + ---------------------- + ----------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ---------------------- + ----------- + ------------------- + ------------------ + | Mysql-bin.000013 | 1150 | aeaiesb | mysql | + ----------------------- + ---------- + ------------------ + 1 row in set (0.00 sec)

The result indicates that the file currently written to the log is a mysql-bin.000013 At 1105, And the slave end needs to synchronize the backup aeaiesb from this point

SlaveEnd

• Stop Database

Service mysql stop

• Modify the configuration file my. cnf and add the following Configuration:

Server-id = 2 Replicate-same-server-id Master-host = 10.254.1.248 Master-user = backup Masters-password = 123456 Master-port = 3306 Master-connect-retry = 60 ---- repeated connection Interval Replicate-do-db = aeaiesb --- synchronize the master's aeaiesb Database Relay-log = miniweb2-relay-bin ---- set the I/O thread to read the binlog of the master and write the local file name, that is, relay-log

• Restart Database

Service mysql start

• On the MySQL command line, view the slave status

Mysql> show slave status/G; * *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.254.1.248 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.42413 Read_Master_Log_Pos: 1105 Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql_bin.42413 Slave_IO_Running: No Slave_ SQL _Running: Yes Replicate_Do_DB: aeaiesb

• In this case, the Slave_IO_Running value is No. If the I/O thread is not started, the synchronization process cannot be performed:

1) in the command line:

(1) slave stop; (2) change master to master_host = '10. 254.1.248 ', master_user = 'backup', master_password = '000000', master_log_file = 'mysql-bin.000014 ', master_log_pos = 123456; (master Status viewed earlier) (3) slave start; (4) show slave status; Mysql> show slave status/G; ***************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.254.1.248 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 1105 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes Slave_ SQL _Running: Yes Replicate_Do_DB: aeaiesb

In this way, you can synchronize data by creating tables in the master's aeaiesb database and inserting data will be displayed on the slave side in real time.

5.3 FAQs 5.3.1 change the MySQL data storage location

First, after MySQL is installed, its database files, configuration files, and command files are not installed in the default directory of SQLServer. It is very important to understand these directories, especially for Linux beginners, the directory structure of Linux itself is complicated. If you cannot figure out the installation directory of MySQL, you won't be able to learn it in depth.

A. Database directory/var/lib/mysql/

B. Configuration File/usr/share/mysql (mysql. server command and configuration file)

C. Related commands/usr/bin (mysqladmin mysqldump and other commands)

D. Start the script/etc/rc. d/init. d/(start the directory of the script file mysql)

Secondly, because the MySQL database directory occupies a large disk, the default data file storage directory of MySQL is/"var/lib/mysql ", you can also move the data directory to the "mysqldata" directory under the "/" root directory.

• First stop MySQL Service

Terminal Command Execution

/Etc/init. d/mysql stop

• Copy all the original mysql database directory files to the new database directory on the terminal.

Cp-r/var/lib/mysql/disk1/mysqldata

• Modify my. cnf configuration file

Back up and modify

Cp/etc/my. cnf/etc/my. cnfbak Vi/etc/my. cnf

Find the datadir code line in the open file, view the following path, and back up the code (add # Before this line of code #)

Original path:/var/lib/mysql

New Path:/disk1/mysqldata

• Modify mysqld

Back up and modify

Cp/etc/init. d/mysqld/etc/init. d/mysqldbak Vi/etc/init. d/mysqld

Original datadir path:/var/lib/mysql

New datadir path:/disk1/mysqldata

• Modify the mysqld_safe File

Cp/usr/bin/mysqld_safe/usr/bin/mysqld_safebak Vi/usr/bin/mysqld_safe

Original datadir path:/var/lib/mysql

New datadir path:/disk1/mysqldata

• Create a mysql. sock Link

Ln-s/disk1/mysqldata/mysql. sock/var/lib/mysql. sock

• Restart service

Service mysqld start

• Enter the MySQL command line to query the data file directory address

Mysql> show variables like '% dir % ';

5.3.2 possible causes of startup failure

When you view the MySQL Log, you can find problems such as Table 'mysql. plugin 'doesn' t exist. This is because the newly installed mysql needs to initialize the database.

Solution:

1) Go To The./mysql/bin directory and execute the script./mysql_install_db;

2) After execution (1), two directory files, mysql and test, will be created under the./mysql/var directory;

3) modify the permissions of mysql and test directories and all files in the directories:

Command:Chown mysql: mysql-R mysql test

• Log error

Mysqld_safe Starting mysqld daemon with databases from/var/lib/mysql [Warning] Can't create test file/var/lib/mysql/localhost. lower-test [Warning] Can't create test file/var/lib/mysql/localhost. lower-test /Usr/sbin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13) [ERROR] Aborting [Note]/usr/sbin/mysqld: Shutdown complete

First, disable the selinux service.

Cd/etc/selinux Vi config

Change selinux to disabled and restart the machine

• Start failed

Fatal error: Can't open and lock privilege tables: Table 'mysql. host' doesn' t exist

Use commands

Mysql_install_db -- user = mysql

Initialization command

• After MySQL is started successfully

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)

Password Reset required

1) first stop the MySQL Service

Service mysql stop

2) restart the service with mysqld_safe

/Usr/bin/mysqld_safe -- skip-grant-tables

3) logon without a password

Mysql-u root

4) Authorization

Grant all privileges on *. * to 'root' @ 'localhost' identified by 'Password' with grant option;

• Authorize the new database file storage address

Chown-R mysql: mysql/disk1/mysqldata

6 related links

Download CentOS image:

Http://www.linuxidc.com/Linux/2007-09/7399p3.htm