FAQs

How secure is the MySQL instance?

Access Security

MySQL instances only support internal network login through cloud hosts and are isolated by account. Therefore, only cloud hosts of the same account can log in to the MySQL instance.

MySQL instances are mandatory authentication, and can only be accessed through authenticated administrator accounts.

Data Security

All MySQL instance data files are stored on hard drives that are protected by RAID1.

MySQL instances are backed up daily, and a manual backup feature is also provided, allowing users to actively back up data at specific points in time.

MySQL instances support the creation of Read-Only instances (set up on different physical machines from the master), which automatically sync data with the master, providing disaster recovery capabilities.

How to dump data into a MySQL instance?

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.

Since there is a file size limit for importing data through the console, it is recommended that users import large files via the command line.

Note:

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

If the entire library is imported, it will overwrite the original basic library of the MySQL instance, affecting subsequent use.

If the data to be imported contains Views or stored procedures, replace the user@host in the sql file with a user with the correct permissions, such as ’root’@’%’, otherwise it may not be imported and used normally.

How to dump data from a MySQL instance?

The following operation will not lock the library:

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

Single-transaction can ensure the consistency of InnoDB tables, but MyISAM tables cannot guarantee consistency. If there are MyISAM tables, you can lock the library and export during the business trough:

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

Note: When dumping, only export the libraries and tables related to the business.

How to create a new user and grant permissions?

Command line operation

Please use the grant command for command line operations.

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

The user logs into the cloud database with the root account, executes show grants, and gets 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 string in the statement, and execute the modified authorization 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 check the running status of the cloud database?

Console Operation

On the MySQL instance management page, select the MySQL instance you want to check. The "Performance Monitoring" on the right side of the details page will display monitoring data such as CPU usage rate, disk usage rate, memory size, memory usage rate, number of connections, QPS, etc.

Command Line Operation

To check the running status of the cloud database:

    show status

To check the running status of the InnoDB engine:

show engine innodb status\G

How is QPS calculated?

QPS sampling calculations are performed every minute. The curve displayed on the performance monitoring page of the details page is at 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

For the convenience of users to view SlowLog, the MySQL instance is set to log_output=table, which will write SlowLog into the mysql.slow_log table. If you need to view 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 the relevant parameters of the cloud database. For specific operations, refer to the configuration file section of the operation guide.

How to install and uninstall plugins?

MySQL instances support the functionality of installing and uninstalling plugins, currently only handlersocket is open.

Command Line Operation

Execute the following statement 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 to make MySQL instances accessible via the Internet?

Recommended method: Use the company's NAT gateway product to implement 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 for port forwarding

Taking the installation of MySQL-Proxy on a CentOS7 cloud host as an example

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

    yum install mysql-proxy

After the installation is complete, you can view related information with the following command:

mysql-proxy -V

View MySQL-Proxy help options:

    mysql-proxy -help -all

The default port for MySQL-Proxy is 4040, and you can access port 3306 by visiting port 4040.

Use the command line to start MySQL-Proxy, the steps are as follows:

    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 the version information above
    
    daemon=true        # mysql-proxy runs as a daemon
    
    keepalive=true        #keep the connection, the startup process will have 2 processes, the first process is used to monitor the second process
    
    proxy-backend-addresses=10.6.X.XX  #target address, udb intranet 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 with 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 a MySQL instance?

Enter the secondary details page of the MySQL instance, you can query the various monitoring items of the instance in the "Performance Monitoring" of the overview page.

Can I view the running status of the cloud database?

Yes, you can. The following are examples of some statements:

View the current connection status:

    show full processlist

View the synchronization status of the replication:

show slave status \G

View the InnoDB status:

    show engine innodb status\G

View the current parameter settings, such as wait_timeout:

    show global variables like '%wait_timeout%';

Which storage engine is the default configuration optimized for?

It is optimized for InnoDB, due to the poor concurrency performance of MyISAM, it is recommended to use InnoDB.

How to set up a cloud database Read-Only instance on a cloud host and synchronize it with the master?

You can set up a replication on the cloud host and synchronize it with the cloud database master.

The specific steps are as follows:

Export data from the cloud database:

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

For using 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 up synchronization.

Step one: Modify the replication configuration file (my.cnf) and add the parameter server-id=1 under [mysqld] (the value can be different from the master)

Execute the following command to make the modification take effect.

    service mysqld reload

Step Two:

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

Step Three:

Find 'change master to' in data.sql, such as:

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

Copy this statement, log in to the replication, 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';

When PORT defaults to 3306, it can be unspecified, then execute replication start.

To check whether the master-replication has been successfully established, you can use:

    show slave status\G;

How to solve the error when using wget to download the Log of UDB on UHost?

When downloading the Log backup of UDB on UHost, you need to add double quotes before and after the URL.

Example: The 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 sets the local name

How to correctly set the character set?

Surfercloud recommends users to set all character sets to UTF-8, because utf8 is compatible with most characters, and the unified character set setting is the most effective way to avoid garbled text. Also, the backend mysqldump and other backup tasks are uniformly using utf8 character set,

We did not detect the character set settings on the server side, other character set settings may cause backup garbled. If your project uses emoji expressions, then we suggest you set all character sets to utf8mb4 (supported after version 5.6).

In addition, UDB does not support setting character set verification rules, it uses the default verification rules of the corresponding character set, unless you set it yourself in the MySQL terminal (we strongly advise against doing this).

There are probably two reasons for the garbled text, one is that the character set of the input (character_set_client) and the output (character_set_results) are inconsistent, which will inevitably lead to garbled text; the second is that although the character set of the input and output are consistent, there is a character set conversion in the middle,

But this conversion process is not lossless, so it may also cause garbled text. Therefore, we suggest that when your program logs into UDB, for example, using utf8, first use this command set names utf8 to ensure that character_set_client, character_set_connection and character_set_results are all utf8,

And set character_set_server, character_set_database, character_set_system all to utf8 in the configuration file, so you can use the default utf8 without specifying the character set when defining the table, this can completely eliminate the problem of garbled text.

How to Query the Client and Server Versions of MySQL Instance

Log in to MySQL, execute the command s, the first line shows the client version, and Server version displays the server version.

    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

How is MySQL Database Master-Replication Synchronization Delay Generated?

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

Network delay: Since MySQL master-replication synchronization is an asynchronous replication based on binlog, the binlog file is transmitted through the network. Network delay is the most common cause of master-replication desynchronization, especially the probability of cross-machine room data synchronization is very high. Therefore, read-write separation is done from the business layer for preliminary design.

Inconsistent load between the master and replication machines: Since MySQL master-replication synchronization starts one IO thread on the master and one SQL thread and one IO thread on the replication, any high load on any machine may cause any thread to lack resources, resulting in master-replication inconsistency.

Inconsistent max_allowed_packet settings: The max_allowed_packet set on the master is larger than the replication. When a large SQL statement can be executed on the master, the setting on the replication is too small to execute, resulting in master-replication inconsistency.

Key auto-increment: The initial key value and auto-increment step setting inconsistency lead to master-replication inconsistency.

MySQL abnormal shutdown: If sync_binlog=1 (default is 0: the executed statement does not synchronize to the hard disk once to the binary log, the performance is the best, and more data is lost during shutdown; 1: each time the binary log is written, it needs to be synchronized with the hard disk, the performance is the worst, and less data is lost during shutdown) or innodb_flush_log_at_trx_commit=1 (default is 1: each transaction commit needs to flush the log to the hard disk, the performance is poor, and less data is lost; 2: write into cache, the log is refreshed to the hard disk every second, the performance is good, and more data is lost) is not set, it is very likely that the binlog or relaylog file will be damaged, causing master-replication inconsistency;

Master-replication desynchronization caused by MySQL's own bugs;

Version inconsistency, especially when the master is a higher version and the replication is a lower version, the features supported by the master are not supported by the replication.

How can users download binlog via API?

If users need to download binlog via API, follow these steps:

List the binlog package list of the specified DB through DescribeUDBInstanceBinlog
Start backup after selecting the binlog file that needs to be backed up through BackupUDBInstanceBinlog
List the successfully backed up binlog file package list of the specified DB through DescribeUDBLogPackage
Get the download URL of the specified binlog backup file package through DescribeUDBBinlogBackupURL

Memory Overflow

Memory overflow, Out Of Memory, abbreviated as OOM, refers to the situation where there is not enough memory space for the program to use when applying for memory, resulting in OOM.

OOM reason: When memory usage exceeds the memory requested by UDB, it will be scored according to a rule of the OS, and the high-scoring process will be killed.

Phenomenon: You can see the memory usage drop sharply through the console monitoring view, and data will appear in the abnormal restart monitoring item.

Measures that can be taken: Confirm that the memory usage parameter configuration is reasonable, confirm whether there are too many slow queries, and confirm whether these memories can meet the current business.

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 users have requirements, they can adjust it on the parameter management page of the console. Different versions provide different maximum values, please refer to the parameter optional maximum value for details.

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

On the console, you can upgrade a regular version instance to a high-availability version by yourself.

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 status: Running, and the database is a single instance without a replication;

User account requirements: The user's account must have a certain balance or credit limit to create a high-availability order. The original regular db order will be deleted when the upgrade process is completed.

Upgrade time selection: It is best to choose a time when the user's business is small or even no business to upgrade.

Impact of the upgrade on business: If the user's regular version database does not have a myisam storage engine, you can complete the upgrade to high availability on the console by yourself, 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 and then upgrade to high availability on the console. The console does not currently support it.

You can also directly use the API to upgrade to high availability:

Pre-upgrade check (refer to the official udb's api documentation for details): Call the CheckUDBInstanceToHAAllowance interface to confirm whether the db can be upgraded. If it cannot, compare the above restrictions on regular upgrades to high availability. If all are met, the interface still returns failure, then contact technical support.

Upgrade process (API):

Step one, call PromoteUDBInstanceToHA, this api builds a dual master for the original db, if the interface returns failure, then contact technical support.

Step two, call the polling DescribeUDBInstanceState to get the status of the db. When the status is WaitForSwitch, execute the following api; if it is in Upgradingha or UpgradehaFail status for a long time, contact technical support.

Step three, when the original db status is WaitForSwitch, call SwitchUDBInstanceToHA, this api will switch the ip of the original regular db to high availability, and regenerate the high availability order and delete the original regular db order; if the interface returns failure, then contact technical support; After the interface returns, refresh the console. If the instance type of the original db becomes a high-availability version, the ip and instance name remain unchanged and the status is running, then the upgrade is successful, otherwise contact technical support.

Can the sync_binlog parameter of High Availability UDB be modified?

The sync_binlog parameter of High Availability UDB is set to 1 by default, which means that fsync is called after each transaction to ensure that binlog is written to the disk. This can prevent data loss, so it is recommended not to modify it!

What do the monitoring items of DB system total memory, memory usage rate, memory size, and DB system total cache mean?

The DB system refers to the DB service process (such as mysqld, mongod, etc.) and the collection of processes that perform daily maintenance on this DB instance (such as backup).

DB system total memory: The sum of the memory overhead of all processes in the DB system and the cache consumed by these processes' IO.

DB system total cache: The sum of the cache consumed by the IO of all processes in the DB system.

Memory size: The memory size occupied by the DB service process (such as mysqld, mongod, etc.).

Memory usage rate: The percentage of memory size to the memory size purchased by the user.

How to choose the storage engine for High Availability UDB?

It is recommended to use the InnoDB engine for High Availability UDB. If you use the non-transactional engine myisam table, it may cause high availability master-replication synchronization abnormalities, which will lead to the failure of high availability disaster recovery.

Will the backup be deleted after the UDB instance is deleted?

After the UDB instance is deleted, the backup (including automatic backup and manual backup) will be kept for 7 days, and it will be automatically recycled after 7 days.

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

Value 0: Indicates that the corresponding monitoring item is normal and does not need attention. Value 1: Indicates that the corresponding monitoring item has an exception and needs attention.

What is the logic and impact of online upgrade and downgrade of High-Availability UDB?

High-Availability UDB supports online upgrade and downgrade, with the specific logic and impact as follows:

Only upgrade memory: Normal online upgrade of memory capacity, high-availability business is not affected, but some configuration parameters related to memory size (such as the cache size inside the database) need to be restarted to take effect. It is recommended that you restart the instance during off-peak business hours after upgrading the memory.
High-Availability UDB upgrades or downgrades the disk or only downgrades the memory: First upgrade or downgrade the standby library of high availability. After the standby library is upgraded or downgraded, the binlog of the main library is retrieved; after the standby library catches up with the binlog, the master-replication switch begins. At this time, there is an impact: the service is read-only and not writable (about 20S of flash cut); after the switch is completed, the original main library is upgraded or downgraded.

What should be noted when rebuilding MySQL replication library?

If you need the UDB team DBA to rebuild the MySQL replication library in the background, please pay attention to the following items:

Based on the data of the UDB master library, if the replication library generates the data of the replication library, it will be overwritten;
During the reconstruction of the replication library, the replication library is not available;
During the reconstruction of the replication library, the IO of the main library will correspondingly limit the speed, and generally will not have much impact.

What are the risks of modifying the log landing strategy of the UDB MySQL high-availability standby library?

If you need the UDB team DBA to modify the log landing strategy of the UDB MySQL high-availability standby library in the background, please pay attention to the following risks:

If the standby library is pulled up in case of a crash, there is a certain chance of causing inconsistency between the master and replication data;
If a disaster recovery occurs, the standby library is promoted to the new master library, and there is a certain chance of causing inconsistency between the master and replication data when a failure occurs again.

Advantages