Ref: https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most
Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual
-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL,
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1
-- MemSQL, Oracle
SELECT 1 FROM DUAL
-- CUBRID
SELECT 1 FROM db_root
-- Db2
SELECT 1 FROM SYSIBM.DUAL
-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1
-- Firebird
SELECT 1 FROM RDB$DATABASE
-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY
-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)
-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual
-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"
I wanted to create a user who had just enough permissions to make a SQL dump of my database for backup purposes. I hunted all over the ‘net, but no one told what permissions were needed so by trial and error I found out:
GRANT SELECT, LOCK TABLES ON *.* TO backup_user@localhost IDENTIFIED BY ‘xxx’;
Now I can have a script that runs with my backup tools that executes:
mysqldump –user backup –password=xxx –all-databases –compact | gzip -9 > db_backup.sql.gz
Ref: https://alibaba-cloud.medium.com/how-to-setup-mariadb-master-and-slave-replication-on-ubuntu-16-04-850c155c5481
MariaDB is a free, open source and one of the most popular open source relational database management system. It is a drop-in replacement for MySQL intended to remain free under the GNU GPL. You will need to increase the instances of your MariaDB server and replicate the data on multiple servers when your traffic grows. The Master-Slave replication provides load balancing for the databases. It is not used for any failover solution.
There are two ways to replicate data:
Master-Master Replication: In this mode, data to be copied from either server. In other words, perform reads or writes from either server. So whenever one server gets the write request it will sync data to other server. This mode will be very useful when you want the best redundancy.
Master-Slave Replication: In this mode, data changes happen on the master server, while the slave server automatically replicates the changes from the master server. This mode will be best suited for data backups.
In this tutorial, we will learn how to set up MariaDB Master-Slave replication on Alibaba Cloud Elastic Compute Service (ECS) with Ubuntu 16.04.
First, log in to your Alibaba Cloud ECS Console. Create a new ECS instance, choosing Ubuntu 16.04 as the operating system with at least 2GB RAM. Connect to your ECS instance and log in as the root user.
Once you are logged into your Ubuntu 16.04 instance, run the following command to update your base system with the latest available packages.
apt-get update -yBefore starting, you will need to install MariaDB server on both instance. You can install it by running the following command:
apt-get install mariadb-server -yOnce the installation is completed, start MariaDB service and enable it to start on boot time with the following command:
systemctl start mysql
systemctl enable mysqlBy default, MariaDB is not secured. So you will need to secure it first. You can do this by running the following command:
mysql_secure_installationAnswer all the questions as shown below:
Set root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] yFirst, you will need to edit /etc/mysql/my.cnf and make some changes inside it.
nano /etc/mysql/my.cnfMake the following changes:
[mysqld]
bind-address = 192.168.0.101
server_id=1
log-basename=master
log-bin=/var/log/mysql/mariadb-bin
binlog-format=row
binlog-do-db=masterdbSave and close the file, when you are finished.
Next, restart MariaDB service to apply the chnages:
systemctl restart mysqlNext, log in to MariaDB shell and configure replication:
mysql -u root -pEnter your root password, then stop the slave and create a replication user and set password:
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';Next, flush the privileges:
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;Next, check the master server status:
MariaDB [(none)]> SHOW MASTER STATUS;Output:
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 615 | masterdb | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)Note: Remember the file mariadb-bin.000001 and position number 615.
Next, exit from the MariaDB shell:
MariaDB [(none)]> exit;Next, take a backup of all databases on the master server and transfer it to the slave server.
First, take all database backup with the following command:
mysqldump --all-databases --user=root --password --master-data > alldatabase.sqlNext, transfer alldatabase.sql file to the slave server with the following command:
scp alldatabase.sql root@192.168.0.102:/root/Next, log in to MariaDB console again and unlock the tables:
mysql -u root -pMariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;
You will also need to edit /etc/mysql/my.cnf file and make some changes inside it:
nano /etc/mysql/my.cnfMake the following changes:
[mysqld]
bind-address = 192.168.0.102
server-id = 2
replicate-do-db=masterdbSave the file, then restart MariaDB service:
systemctl restart mysqlNext, import the alldatabase.sql which you have transferred from master server:
mysql -u root -p < alldatabase.sqlNext, log in to MariaDB shell:
mysql -u root -pEnter your root password, then stop the slave:
MariaDB [(none)]> STOP SLAVE;Next, configure the slave to use the master with the following command:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=615;Next, start the slave and check slave status:
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;Output:
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.20.10.6
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 615
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: masterdbBoth MariaDB master and slave server are now configured. It’s time to test replication.
Navigate to the Master server, and log in to MariaDB shell:
mysql -u root -pEnter your root password, then create a database masterdb which you have specified in my.cnf file:
MariaDB [(none)]> create database masterdb;Next, add some tables and entries on it:
MariaDB [(none)]> use masterdb;
MariaDB [masterdb]> create table mastertable (c int);
MariaDB [masterdb]> insert into mastertable (c) values (1);Now, check the mastertable:
MariaDB [masterdb]> select * from mastertable;Output:
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)Now, navigate to the slave server and verify whether the above created table has been replicated:
First, log in to MariaDB shell:
mysql -u root -pEnter your root password, then change the database to masterdb:
MariaDB [(none)]> use masterdb;Next, check the mastertable:
MariaDB [masterdb]> select * from mastertable;Output:
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)You should see that the database and table are replicated successfully from the master server to the slave server.
Ref: https://stackoverflow.com/questions/57048428/e-package-mysql-client-has-no-installation-candidate-in-php-fpm-image-build-u
php:7.3-fpm now use Debian 10 (Buster) as its base image and Buster ships with MariaDB, so just replace mysql-client with mariadb-client should fix it.
Ref: https://severalnines.com/database-blog/capacity-planning-mysql-and-mariadb-dimensioning-storage-size
Server manufacturers and cloud providers offer different kinds of storage solutions to cater for your database needs. When buying a new server or choosing a cloud instance to run our database, we often ask ourselves - how much disk space should we allocate? As we will find out, the answer is not trivial as there are a number of aspects to consider. Disk space is something that has to be thought of upfront, because shrinking and expanding disk space can be a risky operation for a disk-based database.
In this blog post, we are going to look into how to initially size your storage space, and then plan for capacity to support the growth of your MySQL or MariaDB database.
MySQL stores data in files on the hard disk under a specific directory that has the system variable "datadir". The contents of the datadir will depend on the MySQL server version, and the loaded configuration parameters and server variables (e.g., general_log, slow_query_log, binary log).
The actual storage and retrieval information is dependent on the storage engines. For the MyISAM engine, a table's indexes are stored in the .MYI file, in the data directory, along with the .MYD and .frm files for the table. For InnoDB engine, the indexes are stored in the tablespace, along with the table. If innodb_file_per_table option is set, the indexes will be in the table's .ibd file along with the .frm file. For the memory engine, the data are stored in the memory (heap) while the structure is stored in the .frm file on disk. In the upcoming MySQL 8.0, the metadata files (.frm, .par, dp.opt) are removed with the introduction of the new data dictionary schema.
It's important to note that if you are using InnoDB shared tablespace for storing table data (innodb_file_per_table=OFF), your MySQL physical data size is expected to grow continuously even after you truncate or delete huge rows of data. The only way to reclaim the free space in this configuration is to export, delete the current databases and re-import them back via mysqldump. Thus, it's important to set innodb_file_per_table=ON if you are concerned about the disk space, so when truncating a table, the space can be reclaimed. Also, with this configuration, a huge DELETE operation won't free up the disk space unless OPTIMIZE TABLE is executed afterward.
MySQL stores each database in its own directory under the "datadir" path. In addition, log files and other related MySQL files like socket and PID files, by default, will be created under datadir as well. For performance and reliability reason, it is recommended to store MySQL log files on a separate disk or partition - especially the MySQL error log and binary logs.
The basic way of estimating size is to find the growth ratio between two different points in time, and then multiply that with the current database size. Measuring your peak-hours database traffic for this purpose is not the best practice, and does not represent your database usage as a whole. Think about a batch operation or a stored procedure that runs at midnight, or once a week. Your database could potentially grow significantly in the morning, before possibly being shrunk by a housekeeping operation at midnight.
One possible way is to use our backups as the base element for this measurement. Physical backup like Percona Xtrabackup, MariaDB Backup and filesystem snapshot would produce a more accurate representation of your database size as compared to logical backup, since it contains the binary copy of the database and indexes. Logical backup like mysqldump only stores SQL statements that can be executed to reproduce the original database object definitions and table data. Nevertheless, you can still come out with a good growth ratio by comparing mysqldump backups.
We can use the following formula to estimate the database size:
Where,
The total database size (data and indexes) in MB can be calculated by using the following statements:
1 2 3 4 5 6 | mysql> SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "DB Size in MB" FROM information_schema.tables;+---------------+| DB Size in MB |+---------------+| 2013.41 |+---------------+ |
The above equation can be modified if you would like to use the monthly backups instead. Change the constant value of 52 to 12 (12 months in a year) and you are good to go.
Also, don't forget to account for innodb_log_file_size x 2, innodb_data_file_path and for Galera Cluster, add gcache.size value.
Binary logs are generated by the MySQL master for replication and point-in-time recovery purposes. It is a set of log files that contain information about data modifications made on the MySQL server. The size of the binary logs depends on the number of write operations and the binary log format - STATEMENT, ROW or MIXED. Statement-based binary log are usually much smaller as compared to row-based binary log, because it only consists of the write statements while the row-based consists of modified rows information.
The best way to estimate the maximum disk usage of binary logs is to measure the binary log size for a day and multiply it with the expire_logs_days value (default is 0 - no automatic removal). It's important to set expire_logs_days so you can estimate the size correctly. By default, each binary log is capped around 1GB before MySQL rotates the binary log file. We can use a MySQL event to simply flush the binary log for the purpose of this estimation.
Firstly, make sure event_scheduler variable is enabled:
1 | mysql> SET GLOBAL event_scheduler = ON; |
Then, as a privileged user (with EVENT and RELOAD privileges), create the following event:
1 2 3 4 5 | mysql> USE mysql;mysql> CREATE EVENT flush_binlogON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 2 HOURCOMMENT 'Flush binlogs per hour for the next 2 hours'DO FLUSH BINARY LOGS; |
For a write-intensive workload, you probably need to shorten down the interval to 30 minutes or 10 minutes before the binary log reaches 1GB maximum size, then round the output up to an hour. Then verify the status of the event by using the following statement and look at the LAST_EXECUTED column:
1 2 3 4 | mysql> SELECT * FROM information_schema.events WHERE event_name='flush_binlog'\G ... LAST_EXECUTED: 2018-04-05 13:44:25 ... |
Then, take a look at the binary logs we have now:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> SHOW BINARY LOGS;+---------------+------------+| Log_name | File_size |+---------------+------------+| binlog.000001 | 146 || binlog.000002 | 1073742058 || binlog.000003 | 1073742302 || binlog.000004 | 1070551371 || binlog.000005 | 1070254293 || binlog.000006 | 562350055 | <- hour #1| binlog.000007 | 561754360 | <- hour #2| binlog.000008 | 434015678 |+---------------+------------+ |
We can then calculate the average of our binary logs growth which is around ~562 MB per hour during peak hours. Multiply this value with 24 hours and the expire_logs_days value:
1 2 3 4 5 6 | mysql> SELECT (562 * 24 * @@expire_logs_days);+---------------------------------+| (562 * 24 * @@expire_logs_days) |+---------------------------------+| 94416 |+---------------------------------+ |
We will get 94416 MB which is around ~95 GB of disk space for our binary logs. Slave's relay logs are basically the same as the master's binary logs, except that they are stored on the slave side. Therefore, this calculation also applies to the slave relay logs.
There are two types of I/O operations on MySQL files:
Consider placing random I/O-oriented files in a high throughput disk subsystem for best performance. This could be flash drive - either SSDs or NVRAM card, or high RPM spindle disks like SAS 15K or 10K, with hardware RAID controller and battery-backed unit. For sequential I/O-oriented files, storing on HDD with battery-backed write-cache should be good enough for MySQL. Take note that performance degradation is likely if the battery is dead.
We will cover this area (estimating disk throughput and file allocation) in a separate post.
Capacity planning can help us build a production database server with enough resources to survive daily operations. We must also provision for unexpected needs, account for future storage and disk throughput needs. Thus, capacity planning is important to ensure the database has enough room to breath until the next hardware refresh cycle.
It's best to illustrate this with an example. Considering the following scenario:
If you are using binary logs, sum it up from the value we got in the previous section:
Add at least 100% more room for operational and maintenance tasks (local backup, data staging, error log, operating system files, etc):
Based on this estimation, we can conclude that we would need at least 352 GB of disk space for our database for 3 years. You can use this value to justify your new hardware purchase. For example, if you want to buy a new dedicated server, you could opt for 6 x 128 SSD RAID 10 with battery-backed RAID controller which will give you around 384 GB of total disk space. Or, if you prefer cloud, you could get 100GB of block storage with provisioned IOPS for our 81GB database usage and use the standard persistent block storage for our 95GB binary logs and other operational usage.
Happy dimensioning!
The instructions to install and use xorg-server on macOS via Homebrew: Install Homebrew (if you haven't already): /bin/bash -c ...
