FAQs

How to access MySQL instance?

phpMyAdmin Operation

Select the MySQL instance you need to access, click "Login" on the right side of the list, jump to the phpMyAdmin page, then enter the admin username and password to access the MySQL instance.

Command Line Operation

Access via MySQL Client, log in to the cloud host, and enter the following command in the command line:

mysql -h$IP -P$Port -u$User -p$Password

$IP specifies the intranet IP address of the MySQL instance.

$Port specifies the port of the MySQL instance.

$User specifies the admin username of the MySQL instance.

$Password specifies the admin password of the MySQL instance.

MySQL instances only support intranet login through cloud hosts.

How secure is the MySQL instance?

Access Security

MySQL instances can only be accessed by cloud hosts for intranet login and are isolated by account. Therefore, only cloud hosts of the same account can log in to the MySQL instance.

MySQL instances are forced to authenticate and can only be accessed by authenticated admin accounts.

Data Security

All hard disks containing data files for MySQL instances are protected by RAID1.

MySQL instances perform daily automated backups and also offer manual backup functionality, allowing users to proactively back up data at specific points in time.

The MySQL instance (Master) supports the creation of replicas (deployed on separate physical machines from the master), with automatic data synchronization between the replica and the master, providing disaster recovery capabilities.

How to dump data into a MySQL instance?

phpMyAdmin Operation

Command Line Operation

Upload the data file to be imported to the cloud host and execute the following command in the cloud host:

mysql -h$IP -P$Port -u$User -p$Password < data.sql

data.sql refers to the user's backup file.

Due to file size limitations when importing data via the console, it is recommended that users use the command line interface (CLI) for importing large files.

Note:

When dumping source databases, only export business-related libraries/tables, please filter out the basic libraries mysql/information_schema/performance_schema.

If a full library is imported, it will overwrite the original basic library of MySQL instance and affect subsequent use.

If the data to be imported contains View or stored procedures, the user@host in the SQL file must be replaced with a user that has the appropriate permissions, such as 'root'@'%'. Otherwise, the import and subsequent usage may fail.

How to dump data from a MySQL instance?

The following operations will not lock the library:

mysqldump -h$IP -P$Port -u$User -p$Password --quick --routines --single-transaction --databases db1 db2 db3 > data.sql

The single-transaction option ensures consistency for InnoDB tables, but it cannot guarantee consistency for MyISAM tables. If MyISAM tables are present, consider locking the database during off-peak hours for export.:

mysqldump -h$IP -P$Port -u$User -p$Password --master-data=2 -l --databases db1 db2 db3 > data.sql

Note: During the dump process, only export the business-related libraries/tables.

How to create a new user and grant permissions?

phpMyAdmin Operation

Log in to phpMyAdmin, click on adding a user, enter the username, domain and password; if the domain is '%', select "Any host" in the host column; if specifying a certain subnet, choose "Use text field", then enter the corresponding value, tick the required permissions, after completion, click "Add user".

Command Line Operation

Please use the grant statement for command line operations.

Example: Add a user "<test@%>" with the same permissions as root, with a password of 123456.

Users log in to the cloud database using the root account, execute show grants, and get the root permission statement:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,PROCESS,REFERENCES,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*xxxxxx' WITH GRANT OPTION

Copy this statement, then replace the username, domain, and password strings in the statement, and execute the modified grant statement:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,PROCESS,REFERENCES,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

Refresh the permissions table:

flush privileges;

How to view the running status of the cloud database?

Console Operation

In the list of MySQL instance management pages, select the MySQL instance you need to check. The "Performance Monitoring" in the detail page on the right will display monitoring data such as CPU usage, disk usage, memory size, memory usage, connection number, QPS, etc.

Command Line Operation

Check the running status of the cloud database:

    show status

Check the running status of the InnoDB engine:

show engine innodb status\G

phpMyAdmin Operation

How is QPS counted?

A QPS sampling calculation is performed every minute, and the curve displayed on the detail page's performance monitoring is a 5-minute granularity. If the calculated value is less than 1, it is displayed as 0.

How to view the SlowLog of the MySQL instance?

Command Line Operation

In order to make it easier for users to read SlowLog, the MySQL instance is set to log_output=table, which will log SlowLog into the mysql.slow_log table. If you need to check SlowLog, you can execute:

select * from mysql.slow_log where start_time >= 'xxxx-xx-xx';

How to modify the configuration parameters of the MySQL instance?

MySQL instances allow users to import custom configuration files to modify relevant parameters of the cloud database. For detailed instructions, refer to the configuration file section in user guide.

How to install and uninstall plugins?

MySQL instances support the functions of installing and uninstalling plugins, with only HandlerSocket currently available.

Command Line Operation

Execute the following command to install the plugin:

    install plugin handlersocket soname 'handlersocket.so';

View the list of currently installed plugins:

    show plugins;

Uninstall the plugin：

    uninstall plugin handlersocket;

How can MySQL instances be accessed through the external network?

Recommended method: Use the company's NAT gateway product to achieve port forwarding

For details, please refer to https://docs.ucloud.cn/vpc/guide/natgw (opens in a new tab)

Other methods: Using MySQL-Proxy and other proxy products to achieve port forwarding

Take the example of CentOS7 cloud host installation MySQL-Proxy

Install MySQL-Proxy on the cloud host (UHost):

    yum install mysql-proxy

After the installation, you can check relevant information by the following command:

mysql-proxy -V

View MySQL-Proxy help options：

    mysql-proxy -help -all

MySQL-Proxy default port is 4040, you can access port 3306 by accessing port 4040.

Start MySQL-Proxy with command line, follow these steps：

    touch /etc/mysql-proxy.cnf
    
    vim /etc/mysql-proxy.cnf

Enter the following content：

    [mysql-proxy]
    
    admin-username=root    #admin username
    
    admin-password=123fsck    #admin password
    
    admin-lua-script=/usr/lib64/mysql-proxy/lua/admin.lua    #lua location, see version information above
    
    daemon=true        # mysql-proxy runs as a daemon process
    
    keepalive=true        #Keep the connection, two processes will be started when keepalive is enabled, the first process is used to monitor the second process
    
    proxy-backend-addresses=10.6.X.XX  # Target address, udb internal network address, default port 3306
    
    log-file=/var/log/mysql-proxy.log
    
    log-level=debug

After saving the configuration file, you need to change the permissions：

    chmod 0660 /etc/mysql-proxy.cnf

start：

    mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

You can terminate the program by using the kill command.

Test in an external network environment：

    mysql -h$uhost_ip -P4040 -u$User -p$Password

$uhost_ip is the external IP of UHost.

Note：

You need to open port 4040 of the cloud host (UHost) in the SurferCloud management console.

How to view the various monitoring indicators of the MySQL instance?

Navigate to secondary details page of the MySQL instance, where various monitoring metrics of the instance can be viewed under the 'Performance Monitoring' section on the overview page.

Can I monitor the running status of the cloud database?

Yes. Below are some examples of commands：

Check the current connection status：

    show full processlist

Check the synchronization status of the slave：

show slave status \G

Check InnoDB status：

    show engine innodb status\G

Check the current parameter settings, such as wait_timeout：

    show global variables like '%wait_timeout%';

Why is the DROP database operation prohibited on phpMyAdmin?

Due to the protection mechanism of phpMyAdmin, the DROP command is disabled. If you need to delete the entire library on phpMyAdmin, you need to operate as follows:

Select the corresponding database

Click "Delete", a confirmation prompt will pop up, and then it will be deleted normally.

What storage engine is the default configuration optimized for?

It is optimized for InnoDB. Due to the poor concurrency performance of MyISAM, InnoDB is recommended.

How to build a cloud database replica on a cloud host and perform master-slave synchronization?

You can build a replica on a cloud host and perform master-slave synchronization with the cloud database.

The specific steps are as follows:

Export data from cloud database：

mysqldump -hxxx -uxxx --quick --routines --master-data=2 --single-transaction --databases db1 db2 db3 > data.sql

For those using the MyISAM storage engine library table, use:

    mysqldump -hxxx -uxxx --master-data=2 -l --databases db1 db2 db3 > data.sql

Import data：

    mysql -hxxx -uxxx -p < data.sql

Set synchronization.

First step: Modify the configuration file of the replica (my.cnf) ，add server-id=1（the value should not be the same as the master） under [mysqld]

Execute the following command to make the modification take effect.

    service mysqld reload

Second step：

    grant replication slave on *.* to username@”x.x.x.x” identified by “xxxx”;
    
    flush privileges;

Third step：

In data.sql, find change master to, such as：

    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026',MASTER_LOG_POS=33268716;

Copy this statement, log in to the replica, and complete it as the following command :

    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx,MASTER_HOST='X.X.X.X',
    
    MASTER_PORT=XXX,MASTER_USER='XXX',MASTER_PASSWORD='xxx';

PORT default 3306 can be omitted if not specified, then execute slave start.

To see if the master-slave has been set up successfully, you can use：

    show slave status\G;

How to solve wget download MySQL instance's Log error on cloud host？

When downloading the Log backup of a cloud database on a cloud host, you need to add double quotes before and after the url.

Example: Download address is：http://udbbackup.ufile.ucloud.cn/udb-3u022a/ (opens in a new tab)

    wget -O ppp.tgz "http://udbbackup.ufile.ucloud.cn/udb-3u022a/"

-O Set the local name

How to correctly set the character set？

SurferCloud recommends setting all character sets to UTF-8, because utf8 is compatible with most characters, and a consistent character set setting is the most effective way to avoid garbled characters. The background mysqldump and other backup work also use utf8 character set uniformly,

and the server-side character set configuration was not checked, and other character set configurations may result in backup corruption. If your project uses emoji, it is recommended to set all character sets to utf8mb4 (supported in versions 5.6 and later).

In addition, UDB does not support the setting of character set verification rules, and adopts the default verification rules corresponding to the character set, unless you set it yourself in the MySQL terminal (we strongly advise you not to do so).

There are probably two reasons for the occurrence of garbled characters. One is that the character set for storing (character_set_client) and the extracting character set (character_set_results) are inconsistent, which will inevitably lead to garbled characters; the other is that although the character set for storing and extracting are consistent, there is a character set conversion in the middle,

But this conversion process is not lossless, which may also cause garbled characters. Therefore, we recommend that your application, when connecting to UDB, executes the command SET NAMES utf8 (using utf8 as an example) to ensure that character_set_client, character_set_connection, and character_set_results are all set to utf8.

Additionally, set character_set_server, character_set_database, and character_set_system to utf8 in the configuration file. This way, when defining tables, the character set can be omitted, and the default utf8 will be used, thus completely eliminating any issues related to garbled text.

How to check the client and server versions of a MySQL instance?

    mysql> s
    
    ./mysql Ver 14.14 Distrib 5.6.20-ucloudrel1, for Linux (x86_64) using EditLine wrapper
    
    Connection id: 40491
    
    Current database:
    
    Current user: <ucloudbackup@115.239.196.104>
    
    SSL: Not in use
    
    Current pager: stdout
    
    Using outfile: ''
    
    Using delimiter: ;
    
    Server version: 5.5.24-ucloudrel1-log Source distribution
    
    Protocol version: 10
    
    Connection: 114.119.41.82 via TCP/IP
    
    Server characterset: utf8
    
    Db characterset: utf8
    
    Client characterset: utf8
    
    Conn. characterset: utf8
    
    TCP port: 3370
    
    Uptime: 3 days 8 hours 42 min 18 sec

What causes MySQL database master-slave synchronization delay?

The situations of database master-slave inconsistency are as follows:

Network Latency: MySQL replication is based on binary log (binlog) asynchronous replication, which transmits binlog files over the network. Network latency is a significant factor causing the master-slave desynchronization, especially during data synchronization across data centers, where the likelihood of this issue is notably high. Therefore, it is advisable to implement read-write separation and design the architecture at the business level.

Unequal load on master and slave machines: Mysql master-slave replication starts 1 io thread on the master, while 1 sql thread and 1 io thread on the slave. Any high workload on any of these machines may cause resource shortage on any thread, thus leading to master and slave inconsistency.

Inconsistent max_allowed_packet Settings: If the max_allowed_packet setting on the master is larger than that on the slave, large SQL statements can execute successfully on the master but fail on the slave due to the smaller setting, leading to inconsistencies.

Auto-Increment Key Issues: Inconsistencies between the initial key value and the auto-increment step settings can cause master-slave desynchronization.

MySQL Unexpected Crash: If sync_binlog=1 is not set (default is 0: executed statements are not synchronized to the disk in binary logs, providing optimal performance but resulting in significant data loss during crashes; setting 1: each write to the binary log is synchronized to the disk, resulting in poorer performance but less data loss) or innodb_flush_log_at_trx_commit=1 (default is 1: each transaction commit requires the log to be flushed to the disk, which has poorer performance but less data loss; setting 2: writes are cached, and logs are flushed to the disk every second, offering better performance but potentially more data loss), there is a high likelihood of binlog or relay log file corruption, leading to inconsistencies between master and slave.

Mysql's own bug leads to unsynchronized master-slave;

The versions are inconsistent, especially when the higher version is the master and the lower version is the slave, the features supported by the master are not supported by the slave.

Why can't PMA display after execution?

After the user executes operations in PMA, PMA needs to open the library table to get necessary information to display in PMA, but if the user's view is not reasonable, it will cause read timeout.

For example, the user's custom view needs to query multiple tables or join multiple tables, at this time the SQL statement may need to execute about 30 seconds, the query times out, causing PMA to not display, at this time it is recommended that users use the MySQL client.

How does a user download binlog via API?

If a user needs to download binlog via API, follow these steps:

Use DescribeUDBInstanceBinlog to list the binlog package list of the specified DB
Use BackupUDBInstanceBinlog to start a backup after selecting the binlog file that needs to be backed up
List the binlog file package list with successful backup of the specified DB by DescribeUDBLogPackage
Use DescribeUDBBinlogBackupURL to get the download URL of the specified binlog backup file package

Memory Overflow

Out Of Memory, abbreviated as OOM, refers to a situation where a program does not have enough available memory space when requesting memory, leading to an OOM condition.

Causes of OOM: When memory usage exceeds the memory allocated to UDB, the operating system applies a scoring rule, and processes with higher scores may be killed.

Symptoms: In the monitoring view of the console, a sudden drop in memory usage can be observed, and the monitoring metrics for abnormal restarts will display data.

Recommended Actions: Ensure that memory usage parameters are configured appropriately, check for excessive slow queries, and verify whether the available memory meets the current business requirements.

What is the default maximum number of connections?

Considering the performance and stability of the UDB service, the default maximum number of connections is 2000. If the user has a request, it can be adjusted on the console parameter management page. Different versions provide different maximum values, specifically refer to the maximum optional parameter.

How to upgrade from a regular instance to a high availability instance?

You can upgrade to a highly available version from a regular version instance on the console.

Currently supported upgrade types: mysql-5.5, mysql-5.6, mysql-5.7, mysql-8.0, percona-5.5, percona-5.6, percona-5.7

Currently supported upgrade statuses: running, and this database is a single instance and has no slaves;

User account requirement: the user's account should have certain balance or credit limit, which is used to create highly available orders. The original regular db order will be deleted when the upgrade process is completed.

Upgrade time selection: it's best to choose the time when users have little or no business to do the upgrade.

Impact on the business: If standard version of the user's database does not have a myisam storage engine, it can be upgraded to high availability on the console without any impact, and the impact time is only within one minute (IP drift); if the user's library uses the myisam storage engine, it is recommended to change the storage engine before upgrading to high availability on the console, the console does not support temporarily.

You can also upgrade directly to high availability via API:

Pre-upgrade check (refer to the official udb api documentation for details): Call CheckUDBInstanceToHAAllowance to confirm whether db can be upgraded, if not, compare the above limitations about regular upgrades to high availability, if they are all met, the interface still returns failure, then contact technical support.

Upgrade process (API):

Step one, call PromoteUDBInstanceToHA, this api creates a pair of masters for the original db, if the interface returns failure, contact technical support

Step two, call DescribeUDBInstanceState in a loop, get the status of the db, when the status is WaitForSwitch, execute the following api; if being in Upgradingha for a long time or being UpgradehaFail, then contact technical support

Step three, when the original database status is "WaitForSwitch," call the SwitchUDBInstanceToHA API. This API will switch the IP of the original standard database to high availability, regenerate the high availability order, and delete the original standard database order. If the API call returns a failure, please contact technical support. After receiving the API response, refresh the console. If the instance type of the original database changes to "High Availability," with the IP and instance name remaining unchanged and the status showing "Running," the upgrade is successful; otherwise, please contact technical support.

The meanings of total memory, memory usage, memory size, and total cache in DB system of monitoring items are?

The DB system refers to the collection of DB service processes (mysqld, mongod, etc.) and daily maintenance processes for this DB instance.

Total memory of the DB System：The sum of all processes' memory overheads and their IO consumed cache in the DB system

Total cache of the DB system：The sum of IO consumed cache by all processes in the DB system

Memory size：Memory size occupied by DB service processes (mysqld, mongod, etc.)

Memory usage rate：The percentage of memory size to the memory size purchased by users

Which storage engine does UDB MySQL high availability choose？

It is recommended to use InnoDB engine for UDB MySQL high availability. If you use myisam table of non-transaction engine, it may cause high availability master-slave synchronization abnormal and lead to the failure of high availability disaster recovery.

Will backup be deleted after UDB instance is deleted？

After deleting the UDB instance, the backup (including automatic backup and manual backup) will be retained for 7 days, and will be automatically recycled after 7 days.

What does the value of 0,1 in the cloud database monitoring item mean?

Value 0: Indicates that the corresponding monitoring item is normal and does not need to be paid attention to. Value 1: Indicates that the corresponding monitoring item is abnormal and needs to be monitored.

What is the logic and impact of online upgrade and downgrade of UDB MySQL high availability？

UDB MySQL high availability supports online upgrade and downgrade, the specific logic and impact are as follows:

Only Memory was upgraded, upgrade memory capacity online normally, high availability business will not be affected. But some configuration parameters related to memory size (like the size of database internal cache) need to restart to take effect, it is suggested that you restart the instance after memory upgrade.
UDB MySQL high availability upgrade and downgrade disk or downgrade memory only: Upgrade and downgrade the slave library of high availability first, when the upgrade or downgrade of the slave library is completed, binlog will be recaptured from the main library; After the slave library is chased back to the main library's binlog, start the main and slave switch, at this time there will be an impact: services are read-only but can't be written to (about 20S of flash cut); After the switch is completed, upgrade and downgrade the original main library again.

What should I pay attention to when rebuilding MySQL from library？

If you need to rebuild MySQL from library by the UDB team DBA backend, please note the following:

The data will be based on the UDB master library, and the data generated by the slave library will be overwritten;
During the rebuilding of the slave library, the slave library is not available;
During the rebuilding of the from library, the IO of the master library will correspondingly limit the speed, which generally has little effect.

What are the risks of modifying the log landing strategy of UDB MySQL high availability backup library？

If you need to modify the log landing strategy of UDB MySQL high availability backup library by the UDB team DBA backend, please note the following risks:

If the slave library experiences a crash and pulls back, there is a certain probability that the master-slave data will be inconsistent;
If a disaster occurs, after the slave library is promoted to a new master library, there will be a certain risk of master-slave data inconsistency when it fails again.

Benchmark Test Background and Scheme