Sidebar
public:mysql_database
This is an old revision of the document!
Table of Contents
MySQL Database Backup Set
AhsayOBM allows you to back up databases in your MySQL Server with the MySQL Database Backup Set.
System Requirement
For the list of supported operating systems & application versions, refer to: Ahsay Software Compatibility List (SCL)
Requirements and Recommendations for Linux
Please ensure that the following requirements and conditions are met on the MySQL database server.
Required packages
Make sure that the following packages have to be present on the Linux machine in order for AhsayOBM version 8 to be installed.
- OpenJDK Version 1.8 (https://www.freebsd.org/java/)
The installed Java Development Kit version must at least be 8. - GNU LIBC 2.14 (https://www.gnu.org/software/libc/)
The installed ‘GNU LIBC’ version must at least be 2.14 for OpenJDK 8 to work.
Root account installation
AhsayOBM is installed on the MySQL database server using the root account.
Online database instance
Make sure the MySQL database instance is online.
Example: MySQL5.7 on CentOS 7
# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-01-02 11:42:08 HKT; 2h 46min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 16952 (mysqld)
CGroup: /system.slice/mysqld.service
└─16952 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/my...
Jan 02 11:42:00 centos7 systemd[1]: Starting MySQL Server...
Jan 02 11:42:08 centos7 systemd[1]: Started MySQL Server.
Listening Port
Check the listening port of the MySQL database instance (default is 3306).
#netstat -pan|more Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1/systemd tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 21981/java tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 2489/dnsmasq tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1307/sshd tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1110/cupsd tcp 0 0 127.0.0.1:60024 0.0.0.0:* LISTEN 21981/java tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 15556/master tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 21981/java tcp 86 0 10.16.30.2:37272 203.186.85.237:443 CLOSE_WAIT 21981/java tcp 86 0 10.16.30.2:49302 40.114.13.14:443 CLOSE_WAIT 21981/java tcp 0 64 10.16.30.2:22 192.168.12.1:55777 ESTABLISHED 14094/sshd: root@pt tcp6 0 0 :::111 :::* LISTEN 1/systemd tcp6 0 0 :::22 :::* LISTEN 1307/sshd tcp6 0 0 ::1:631 :::* LISTEN 1110/cupsd tcp6 0 0 ::1:25 :::* LISTEN 15556/master tcp6 0 0 :::3306 :::* LISTEN 15671/mysqld tcp6 86 0 10.16.30.2:48396 10.16.30.21:443 CLOSE_WAIT 22347/bschJW tcp6 86 0 10.16.30.2:48428 10.16.30.21:443 CLOSE_WAIT 22347/bschJW
mysqldump utility
The mysqldump utility is installed on the MySQL database server. To locate the mysqldump utility use the whereis command:
#whereis mysqldump mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz
mysqldump version
The mysqldump utility is the same version as the MySQL database.
Example 1: MySQL 5.7 on CentOS 7.3
i. mysqldump version
#mysqldump --version mysqldump Ver 10.13 Distrib 5.7.24, for Linux (x86_64)
ii. MySQL database version
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.24 | +-----------+ 1 row in set (0.00 sec)
Example 2: MySQL 8 on CentOS 7.4
i. mysqldump version
# mysqldump --version mysqldump Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
ii. MySQL database version
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec)
MySQL database user account with correct privileges
A MySQL database user account with the following privileges must be setup for the backup operation.
Example 1: MySQL 5.7
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost.localdomain’ IDENTIFIED BY ‘password’; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>
Example 2: MySQL 8
For MySQL 8 the use of GRANT to define account authentication characteristic is deprecated. For more information please refer to the MySQL 8.0 Reference Manual. As an alternative, you must first create the user and set the authentication characteristic by using CREATE USER before setting the privileges of the user using GRANT.
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’; Query OK, 0 rows affected (0.08 sec) mysql> CREATE USER 'username'@'localhost.localdomain' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.46 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost.localdomain’; Query OK, 0 rows affected (0.22 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.23 sec)
Resolvable localhost
Verify that “localhost” on the MySQL database server is resolvable and “localhost” is allowed to access the MySQL database instance on the MySQL service listening port (default 3306).
#ping localhost PING localhost (127.0.0.1) 56(84) bytes of data. 64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.189 ms 64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.054 ms 64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.054 ms 64 bytes from localhost (127.0.0.1): icmp_seq=4 ttl=64 time=0.057 ms 64 bytes from localhost (127.0.0.1): icmp_seq=5 ttl=64 time=0.057 ms --- localhost ping statistics --- 5 packets transmitted, 5 received, 0% packet loss, time 4000ms rtt min/avg/max/mdev = 0.054/0.082/0.189/0.053 ms #telnet 127.0.0.1 3306 Trying 127.0.0.1... Connected to 127.0.0.1. Escape character is '^]'. J 5.7.24i6&VeFS!F<t'zmysql_native_password
==== Exclude “information_schema” and “performance_schema” databases Exclude “information_schema” and “performance_schema” databases since they are MySQL virtual system databases, which contains information about the user databases on the MySQL instance. They are read-only and cannot be backed up.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
Temporary directory
The databases selected for backup will be temporarily spooled to a temporary directory before being uploaded to the backup server or destination storage.
Ensure that the temporary directory configured for the MySQL database backup has sufficient disk space for the backup operation, the free space on the temporary directory drive should be at least 130% of the database size. As the temporary directory is also used for storing index files and any incremental or differential delta files generated during the backup job before they are uploaded to the backup destination.
Please bear in mind the size of the databases may grow over time and you may need to review the temporary directory free space requirements on a regular basis. To calculate for the size of your databases run the command below.
mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+-----------+ | Database | Size (MB) | +--------------------+-----------+ | information_schema | 0.16 | | mysql | 2.43 | | performance_schema | 0.00 | | sys | 0.02 | +--------------------+-----------+ 4 rows in set (0.04 sec) mysql>
Documentation
Issues
public/mysql_database.1566187762.txt.gz · Last modified: 2019/08/19 12:09 by ronnie.chan