User Tools

Site Tools


public:mysql_database

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
public:mysql_database [2019/08/19 11:52]
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 7: Line 9:
 For the list of supported operating systems & application versions, refer to: [[public:​8001_faq:​ahsay_software_compatibility_list_scl_for_version_8.1_or_above|Ahsay Software Compatibility List (SCL)]] For the list of supported operating systems & application versions, refer to: [[public:​8001_faq:​ahsay_software_compatibility_list_scl_for_version_8.1_or_above|Ahsay Software Compatibility List (SCL)]]
  
-===== Requirements and Recommendations ===== +---- 
 + 
 +===== 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 17: 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 41: Line 49:
 </​code>​ </​code>​
  
 +=== Listening Port ===
 +Check the listening port of the MySQL database instance (default is 3306).
 +<​code>​
 +#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
 +</​code>​
 +
 +=== mysqldump utility ===
 +The mysqldump utility is installed on the MySQL database server.
 +To locate the mysqldump utility use the whereis command:
 +<​code>​
 +#whereis mysqldump
 +mysqldump: /​usr/​bin/​mysqldump /​usr/​share/​man/​man1/​mysqldump.1.gz
 +</​code>​
 +
 +=== mysqldump version ===
 +The mysqldump utility is the same version as the MySQL database.
 +<WRAP indent>
 +
 +== Example 1: MySQL 5.7 on CentOS 7.3 ==
 +
 +i. mysqldump version
 +<​code>​
 +#mysqldump --version
 +mysqldump ​ Ver 10.13 Distrib 5.7.24, for Linux (x86_64)
 +</​code>​
 +
 +ii. MySQL database version
 +<​code>​
 +mysql> select version();
 ++-----------+
 +| version() |
 ++-----------+
 +| 5.7.24 ​   |
 ++-----------+
 +1 row in set (0.00 sec)
 +</​code>​
 +
 +== Example 2: MySQL 8 on CentOS 7.4 ==
 +i. mysqldump version
 +<​code>​
 +# mysqldump --version
 +mysqldump ​ Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
 +</​code>​
 +
 +ii. MySQL database version
 +<​code>​
 +mysql> select version();
 ++-----------+
 +| version() |
 ++-----------+
 +| 8.0.11 ​   |
 ++-----------+
 +1 row in set (0.00 sec)
 +</​code>​
 +</​WRAP>​
 +
 +=== MySQL database user account with correct privileges ===
 +A MySQL database user account with the following privileges must be setup for the backup operation.
 +<WRAP indent>
 +== Example 1: MySQL 5.7 ==
 +<​code>​
 +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>
 +</​code>​
 +
 +== 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.
 +<​code>​
 +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)
 +</​code>​
 +</​WRAP>​
 +
 +=== 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).
 +<​code>​
 +#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
 +</​code>​
 +
 +<​code>​
 +#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
 +</​code>​
 +
 +=== 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.
 +<​code>​
 +mysql> show databases;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| performance_schema |
 +| sys                |
 ++--------------------+
 +4 rows in set (0.00 sec)
 +
 +mysql>
 +</​code>​
 +
 +=== 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.
 +
 +<WRAP prewrap>
 +<​code>​
 +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>
 +</​code>​
 +</​WRAP>​
 +</​WRAP>​
 +
 +----
 +
 +==== Windows ==== 
 +
 +<WRAP indent>
 +
 +=== AhsayOBM ===
 +AhsayOBM is installed on the MySQL database server.
 +
 +=== Online database instance === 
 +The MySQL database instance is online.
 +
 +<WRAP indent>
 +== 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}}
 +
 +== Example 2: Example: MySQL v8 on Windows Server 2016, the default service name is MySQL80. ==
 +
 +{{public:​ahsay_wiki_module_mysql_windows_2.png}}
 +
 +</​WRAP>​
 +
 +=== Listening port ===
 +Check the listening port of the MySQL database instance (default is 3306) using the command netstat –b –a.
 +<​code>​
 +C:​\>​netstat -b -a
 +
 +Active Connections
 +
 +  Proto  Local Address ​         Foreign Address ​       State
 +  TCP    0.0.0.0:​135 ​           w2k16-std:​0 ​           LISTENING
 +  RpcSs
 + ​[svchost.exe]
 +  TCP    0.0.0.0:​445 ​           w2k16-std:​0 ​           LISTENING
 + Can not obtain ownership information
 +  TCP    0.0.0.0:​2179 ​          ​w2k16-std:​0 ​           LISTENING
 + ​[vmms.exe]
 +  TCP    0.0.0.0:​3306 ​          ​w2k16-std:​0 ​           LISTENING
 + ​[mysqld.exe]
 +  TCP    0.0.0.0:​3389 ​          ​w2k16-std:​0 ​           LISTENING
 +  TermService
 + ​[svchost.exe]
 +  TCP    0.0.0.0:​5985 ​          ​w2k16-std:​0 ​           LISTENING
 + Can not obtain ownership information
 +  TCP    0.0.0.0:​47001 ​         w2k16-std:​0 ​           LISTENING
 + Can not obtain ownership information
 +  TCP    0.0.0.0:​49664 ​         w2k16-std:​0 ​           LISTENING
 + Can not obtain ownership information
 +  TCP    0.0.0.0:​49665 ​         w2k16-std:​0 ​           LISTENING
 + ​[lsass.exe]
 +  TCP    0.0.0.0:​49666 ​         w2k16-std:​0 ​           LISTENING
 +  EventLog
 + ​[svchost.exe]
 +  TCP    0.0.0.0:​49667 ​         w2k16-std:​0 ​           LISTENING
 + ​[spoolsv.exe]
 +  TCP    0.0.0.0:​49668 ​         w2k16-std:​0 ​           LISTENING
 +  SessionEnv
 + ​[svchost.exe]
 +  TCP    0.0.0.0:​49669 ​         w2k16-std:​0 ​           LISTENING
 +  PolicyAgent
 +</​code>​
 +
 +=== mysqldump utility ===
 +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
 +
 +=== mysqldump version ===
 +The mysqldump utility is the same version as the MySQL database.
 +To check the mysqldump version use the mysqldump – –version command.
 +<WRAP indent>
 +== Checking mysqldump version ==
 +To check the mysqldump version use the mysqldump – –version command.
 +
 += Example 1: MySQL v5.6 =
 +<​code>​
 +C:\Program Files\MySQL\MySQL Server 5.6\bin>​mysqldump --version
 +mysqldump ​ Ver 10.13 Distrib 5.6.41, for Win64 (x86_64)
 +
 +C:\Program Files\MySQL\MySQL Server 5.6\bin>
 +</​code>​
 +
 += Example 2: MySQL v8.0 =
 +<​code>​
 +C:\Program Files\MySQL\MySQL Server 8.0\bin>​mysqldump --version
 +mysqldump ​ Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL)
 +
 +C:\Program Files\MySQL\MySQL Server 8.0\bin>
 +</​code>​
 +
 +== Checking MySQL database version ==
 +
 += Example 1: MySQL v5.6 =
 +<​code>​
 +mysql> select version();
 ++------------+
 +| version() ​ |
 ++------------+
 +| 5.6.41-log |
 ++------------+
 +1 row in set (0.00 sec)
 +
 +mysql>
 +</​code>​
 +
 += Example 2: MySQL v8.0 =
 +<​code>​
 +mysql> select version();
 ++-----------+
 +| version() |
 ++-----------+
 +| 8.0.12 ​   |
 ++-----------+
 +1 row in set (0.00 sec)
 +mysql>
 +</​code>​
 +
 +</​WRAP>​
 +
 +=== MySQL database user account ===
 +A MySQL database user account with the following privileges must be setup for the backup operation. ​
 +
 +<WRAP indent>
 +
 +== Example 1: MySQL v5.6 ==
 +<​code>​
 +mysql> GRANT ALL PRIVILEGES ON *.* TO "​username"​@"​localhost"​ IDENTIFIED BY "​password";​
 +Query OK, 0 rows affected (0.00 sec)
 +
 +mysql> GRANT ALL PRIVILEGES ON *.* TO "​username"​@"​localhost.localdomain"​ IDENTIFIED BY "​password";​
 +Query OK, 0 rows affected (0.00 sec)
 +
 +mysql> FLUSH PRIVILEGES;
 +Query OK, 0 rows affected (0.01 sec)
 +
 +mysql>
 +</​code>​
 +
 +== 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. ​
 +<​code>​
 +mysql> CREATE USER '​root'​@'​localhost.localdomain'​ IDENTIFIED BY '​Abcd123$%';​
 +Query OK, 0 rows affected (0.32 sec)
 +
 +mysql> GRANT ALL PRIVILEGES ON *.* TO '​root'​@'​localhost';​
 +Query OK, 0 rows affected (0.01 sec)
 +
 +mysql> GRANT ALL PRIVILEGES ON *.* TO '​root'​@'​localhost.localdomain';​
 +Query OK, 0 rows affected (0.12 sec)
 +
 +mysql> FLUSH PRIVILEGES;
 +Query OK, 0 rows affected (0.01 sec)
 +</​code>​
 +</​WRAP>​
 +
 +=== 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).
 +<​code>​
 +C:\>ping localhost
 +
 +Pinging 10.90.10.40 with 32 bytes of data:
 +Reply from 10.90.10.40:​ bytes=32 time<1ms TTL=64
 +Reply from 10.90.10.40:​ bytes=32 time<1ms TTL=64
 +Reply from 10.90.10.40:​ bytes=32 time<1ms TTL=64
 +Reply from 10.90.10.40:​ bytes=32 time<1ms TTL=64
 +
 +Ping statistics for 10.90.10.40:​
 +    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
 +Approximate round trip times in milli-seconds:​
 +    Minimum = 0ms, Maximum = 0ms, Average = 0ms
 +
 +C:\>
 +</​code>​
 +
 +<​code>​
 +# telnet localhost 3306
 +Trying 127.0.0.1...
 +Connected to localhost.
 +Escape character is '​^]'​
 +J
 +5.6.31vB#'​8%/​kQ3K\n6``Aemysql_native_password
 +</​code>​
 +
 +<WRAP prewrap>
 +<​code>​
 +NOTE:
 +The telnet utility is not installed by default on some Windows versions.
 +</​code>​
 +</​WRAP>​
 +
 +=== 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.
 +<​code>​
 +mysql> show databases;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| performance_schema |
 +| sakila ​            |
 +| test               |
 +| world              |
 ++--------------------+
 +6 rows in set (0.00 sec)
 +</​code>​
 +
 +=== 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.
 +
 +<​code>​
 ++--------------------+-----------+
 +| Database ​          | Size (MB) |
 ++--------------------+-----------+
 +| information_schema |      0.01 |
 +| mysql              |      0.90 |
 +| performance_schema |      0.00 |
 +| sakila ​            ​| ​     6.44 |
 +| world              |      0.77 |
 ++--------------------+-----------+
 +5 rows in set (0.53 sec)
 +</​code>​
 +
 +=== Limitations for MySQL running on Windows ===
 +
 +  - Backup and restore must be to the same MySQL database version.
 +  - When restoring MySQL databases to an alternate location only one database can be selected and restored at any one time.
 +  - Cannot restore the MySQL database nodes to original or alternate location.
 +  - Restoring databases to another machine can only be done using the Restore raw file option.
 +
 +</​WRAP>​
 +----
  
 ===== Documentation ===== ===== Documentation =====
public/mysql_database.1566186757.txt.gz · Last modified: 2019/08/19 11:52 by ronnie.chan

Page Tools