public:mysql_database
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
public:mysql_database [2019/08/19 14:00] ronnie.chan |
public:mysql_database [2022/11/28 11:07] (current) kirk.lim Last modified: 2019/08/19 14:05 by ronnie.chan |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== MySQL Database Backup Set ====== | ====== MySQL Database Backup Set ====== | ||
| + | |||
| + | Last modified: 2019/08/19 (Note: Content written for AhsayCBS v7+v8, and may generally apply to latest product release) | ||
| AhsayOBM allows you to back up databases in your MySQL Server with the MySQL Database Backup Set. | AhsayOBM allows you to back up databases in your MySQL Server with the MySQL Database Backup Set. | ||
| Line 9: | Line 11: | ||
| ---- | ---- | ||
| - | ===== Requirements and Recommendations for Linux ===== | + | ===== Requirements and Recommendations===== |
| + | |||
| + | ==== Linux ==== | ||
| Please ensure that the following requirements and conditions are met on the MySQL database server. | Please ensure that the following requirements and conditions are met on the MySQL database server. | ||
| - | ==== Required packages ==== | + | <WRAP indent> |
| + | |||
| + | === 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. | Make sure that the following packages have to be present on the Linux machine in order for AhsayOBM version 8 to be installed. | ||
| Line 19: | Line 25: | ||
| * 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. | * 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 ==== | + | === Root account installation === |
| AhsayOBM is installed on the MySQL database server using the root account. | AhsayOBM is installed on the MySQL database server using the root account. | ||
| - | ==== Online database instance ==== | + | === Online database instance === |
| Make sure the MySQL database instance is online. | Make sure the MySQL database instance is online. | ||
| Line 43: | Line 49: | ||
| </code> | </code> | ||
| - | ==== Listening Port ==== | + | === Listening Port === |
| Check the listening port of the MySQL database instance (default is 3306). | Check the listening port of the MySQL database instance (default is 3306). | ||
| <code> | <code> | ||
| Line 69: | Line 75: | ||
| </code> | </code> | ||
| - | ==== mysqldump utility ==== | + | === mysqldump utility === |
| The mysqldump utility is installed on the MySQL database server. | The mysqldump utility is installed on the MySQL database server. | ||
| To locate the mysqldump utility use the whereis command: | To locate the mysqldump utility use the whereis command: | ||
| Line 77: | Line 83: | ||
| </code> | </code> | ||
| - | ==== mysqldump version ==== | + | === mysqldump version === |
| The mysqldump utility is the same version as the MySQL database. | The mysqldump utility is the same version as the MySQL database. | ||
| <WRAP indent> | <WRAP indent> | ||
| - | === Example 1: MySQL 5.7 on CentOS 7.3 === | + | == Example 1: MySQL 5.7 on CentOS 7.3 == |
| i. mysqldump version | i. mysqldump version | ||
| Line 100: | Line 106: | ||
| </code> | </code> | ||
| - | === Example 2: MySQL 8 on CentOS 7.4 === | + | == Example 2: MySQL 8 on CentOS 7.4 == |
| i. mysqldump version | i. mysqldump version | ||
| <code> | <code> | ||
| Line 119: | Line 125: | ||
| </WRAP> | </WRAP> | ||
| - | ==== MySQL database user account with correct privileges ==== | + | === MySQL database user account with correct privileges === |
| A MySQL database user account with the following privileges must be setup for the backup operation. | A MySQL database user account with the following privileges must be setup for the backup operation. | ||
| <WRAP indent> | <WRAP indent> | ||
| - | === Example 1: MySQL 5.7 === | + | == Example 1: MySQL 5.7 == |
| <code> | <code> | ||
| mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’; | mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’; | ||
| Line 136: | Line 142: | ||
| </code> | </code> | ||
| - | === Example 2: MySQL 8 === | + | == 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. | 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. | ||
| <code> | <code> | ||
| Line 150: | Line 156: | ||
| </WRAP> | </WRAP> | ||
| - | ==== Resolvable localhost ==== | + | === 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). | 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). | ||
| <code> | <code> | ||
| Line 164: | Line 170: | ||
| 5 packets transmitted, 5 received, 0% packet loss, time 4000ms | 5 packets transmitted, 5 received, 0% packet loss, time 4000ms | ||
| rtt min/avg/max/mdev = 0.054/0.082/0.189/0.053 ms | rtt min/avg/max/mdev = 0.054/0.082/0.189/0.053 ms | ||
| + | </code> | ||
| + | <code> | ||
| #telnet 127.0.0.1 3306 | #telnet 127.0.0.1 3306 | ||
| Trying 127.0.0.1... | Trying 127.0.0.1... | ||
| Line 173: | Line 181: | ||
| </code> | </code> | ||
| - | ==== Exclude "information_schema" and "performance_schema" databases ==== | + | === 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. | 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. | ||
| <code> | <code> | ||
| Line 190: | Line 198: | ||
| </code> | </code> | ||
| - | ==== Temporary directory ==== | + | === 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. | The databases selected for backup will be temporarily spooled to a temporary directory before being uploaded to the backup server or destination storage. | ||
| Line 213: | Line 221: | ||
| mysql> | mysql> | ||
| </code> | </code> | ||
| + | </WRAP> | ||
| </WRAP> | </WRAP> | ||
| ---- | ---- | ||
| - | ===== Requirements and Recommendations for Windows ===== | + | ==== Windows ==== |
| - | ==== AhsayOBM ==== | + | <WRAP indent> |
| + | |||
| + | === AhsayOBM === | ||
| AhsayOBM is installed on the MySQL database server. | AhsayOBM is installed on the MySQL database server. | ||
| - | ==== Online database instance ==== | + | |
| + | === Online database instance === | ||
| The MySQL database instance is online. | The MySQL database instance is online. | ||
| <WRAP indent> | <WRAP indent> | ||
| - | === Example 1: MySQL v5.6 on Windows Server 2016 (64-bit), the default service name is MySQL56. === | + | == Example 1: MySQL v5.6 on Windows Server 2016 (64-bit), the default service name is MySQL56. == |
| {{public:ahsay_wiki_module_mysql_windows_1.png}} | {{public:ahsay_wiki_module_mysql_windows_1.png}} | ||
| - | === Example 2: Example: MySQL v8 on Windows Server 2016, the default service name is MySQL80. === | + | == Example 2: Example: MySQL v8 on Windows Server 2016, the default service name is MySQL80. == |
| {{public:ahsay_wiki_module_mysql_windows_2.png}} | {{public:ahsay_wiki_module_mysql_windows_2.png}} | ||
| Line 235: | Line 247: | ||
| </WRAP> | </WRAP> | ||
| - | ==== Listening port ==== | + | === Listening port === |
| Check the listening port of the MySQL database instance (default is 3306) using the command netstat –b –a. | Check the listening port of the MySQL database instance (default is 3306) using the command netstat –b –a. | ||
| <code> | <code> | ||
| Line 275: | Line 287: | ||
| </code> | </code> | ||
| - | ==== mysqldump utility ==== | + | === mysqldump utility === |
| The mysqldump utility is installed on the MySQL database server. | The mysqldump utility is installed on the MySQL database server. | ||
| Example: the default location for the mysqldump utitlity for MySQL v5.6.x is located in the following folder C:\Program Files\MySQL\MySQL Server 5.6\bin | Example: the default location for the mysqldump utitlity for MySQL v5.6.x is located in the following folder C:\Program Files\MySQL\MySQL Server 5.6\bin | ||
| - | ==== mysqldump version ==== | + | === mysqldump version === |
| The mysqldump utility is the same version as the MySQL database. | The mysqldump utility is the same version as the MySQL database. | ||
| To check the mysqldump version use the mysqldump – –version command. | To check the mysqldump version use the mysqldump – –version command. | ||
| <WRAP indent> | <WRAP indent> | ||
| - | === Checking mysqldump version === | + | == Checking mysqldump version == |
| To check the mysqldump version use the mysqldump – –version command. | To check the mysqldump version use the mysqldump – –version command. | ||
| - | == Example 1: MySQL v5.6 == | + | = Example 1: MySQL v5.6 = |
| <code> | <code> | ||
| C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --version | C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --version | ||
| Line 294: | Line 306: | ||
| </code> | </code> | ||
| - | == Example 2: MySQL v8.0 == | + | = Example 2: MySQL v8.0 = |
| <code> | <code> | ||
| C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump --version | C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump --version | ||
| Line 302: | Line 314: | ||
| </code> | </code> | ||
| - | === Checking MySQL database version === | + | == Checking MySQL database version == |
| - | == Example 1: MySQL v5.6 == | + | = Example 1: MySQL v5.6 = |
| <code> | <code> | ||
| mysql> select version(); | mysql> select version(); | ||
| Line 317: | Line 329: | ||
| </code> | </code> | ||
| - | == Example 2: MySQL v8.0 == | + | = Example 2: MySQL v8.0 = |
| <code> | <code> | ||
| mysql> select version(); | mysql> select version(); | ||
| Line 331: | Line 343: | ||
| </WRAP> | </WRAP> | ||
| - | ==== MySQL database user account ==== | + | === MySQL database user account === |
| A MySQL database user account with the following privileges must be setup for the backup operation. | A MySQL database user account with the following privileges must be setup for the backup operation. | ||
| <WRAP indent> | <WRAP indent> | ||
| - | === Example 1: MySQL v5.6 === | + | == Example 1: MySQL v5.6 == |
| <code> | <code> | ||
| mysql> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost" IDENTIFIED BY "password"; | mysql> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost" IDENTIFIED BY "password"; | ||
| Line 350: | Line 362: | ||
| </code> | </code> | ||
| - | === Example 1: MySQL v8.0 === | + | == Example 1: MySQL v8.0 == |
| 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. | 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. | ||
| <code> | <code> | ||
| Line 367: | Line 379: | ||
| </WRAP> | </WRAP> | ||
| - | ==== Resolvable localhost ==== | + | === 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). | 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). | ||
| <code> | <code> | ||
| Line 402: | Line 414: | ||
| </WRAP> | </WRAP> | ||
| - | ==== Exclude "information_schema" and "performance_schema" databases ==== | + | === Exclude "information_schema" and "performance_schema" databases === |
| Exclude the "information_schema" and "performance_schema" databases 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. | Exclude the "information_schema" and "performance_schema" databases 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. | ||
| <code> | <code> | ||
| Line 419: | Line 431: | ||
| </code> | </code> | ||
| - | ==== Temporary directory ==== | + | === 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. | 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. | 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. | ||
| Line 439: | Line 451: | ||
| </code> | </code> | ||
| - | ==== Limitations for MySQL running on Windows ==== | + | === Limitations for MySQL running on Windows === |
| - Backup and restore must be to the same MySQL database version. | - Backup and restore must be to the same MySQL database version. | ||
| Line 446: | Line 458: | ||
| - Restoring databases to another machine can only be done using the Restore raw file option. | - Restoring databases to another machine can only be done using the Restore raw file option. | ||
| + | </WRAP> | ||
| ---- | ---- | ||
public/mysql_database.1566194405.txt.gz · Last modified: 2019/08/19 14:00 by ronnie.chan