mysql

Docker MySQL Backup to Local

2022-03-27 · 3 min read · docker container backup mysql ·

Doing a backup of your mysql container's DB can be automated just like any other task. Script below is one way to do it. You may or may not provide a parameter to the script and you'll always get the gzipped version of the backup + its sha256 checksum. Sample Use Case: use it without parameters 1~$ ./backup_db.sh …

Check if databases are empty

2020-09-02 · 1 min read · mysql mariadb databases ·

Share on:

To check if MariaDB/MySQL databases are empty in your DB Server, run the following query: 1MariaDB [(none)]> SELECT table_schema, # "DB Name", 2-> Round(Sum(data_length + index_length) / 1024 / 1024, 1) # "DB Size in MB" 3-> FROM information_schema.tables 4-> GROUP BY table_schema; …

MySQLDump Except Locked Tables

2020-06-17 · 1 min read · mysql mariadb dump mysqldump ·

Share on:

MySQL will go down if locked tables are touched during dump. Use the following mysqldump syntax instead: 1mysqldump --single-transaction --skip-lock-tables

Truncate Table

2020-06-10 · 1 min read · mysql mariadb delete truncate ·

Share on:

To delete the entries BUT not the table, use: 1TRUNCATE TABLE <table_name>; See details in https://www.mysqltutorial.org/mysql-truncate-table/

MySQL/MariaDB with Credentials File

2020-05-12 · 1 min read · mysql mariadb authentication permission credentials credentials-file mysql-security ·

Share on:

Using a configuration file for mysql is a good practice as you don't immediately expose credentials. Below, you are going to mysql cli and immediately using the db named dbName. 1mysql --defaults-file=/path/to/credentials.ini dbName where credentials.ini has this format: 1[client] …

Cannot write pid file

2020-02-19 · 1 min read · mysql mariadb pid permission ·

Share on:

MariaDB seems to have this issue and can't start as shown in /var/log/mariadb/mariadb.log 12020-02-19 15:18:49 0 [ERROR] mysqld: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 2 "No such file or directory") I was able to fix one instance of this issue by doing: 1sudo mkdir …

Show Table Sizes

2020-01-21 · 1 min read · mysql mariadb table sizes ·

Share on:

Use the query below and replace "database_name" with the correct db name. 1SELECT table_name AS "Table", 2ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 3FROM information_schema.TABLES 4WHERE table_schema = "database_name" 5ORDER BY (data_length + index_length) …

Shrink Huge ibdata1

2019-11-24 · 1 min read · mysql mariadb innodb optimization ·

Share on:

Best way is to make one file per table and not use default which baloons to a huge file and never shrinks when some tables are removed and unused. See https://www.thegeekstuff.com/2016/02/mysql-innodb-file-per-table Some other reference: …

Dump MySQL Database but Skip Some Table(s)

2019-11-24 · 1 min read · mysql mariadb tables dump cli mysqldump database ·

Share on:

1mysqldump -u<username> -p<password> --no-create-db myDB --ignore-table=myDB.table1 --ignore-table=myDB.table3-h localhost > mysql.backup See https://tecadmin.net/tutorial/mysql/skip-tables-in-mysqldump/

Slow Query Logs

2019-02-12 · 1 min read · mysql mariadb logs slow slow-query ·

Share on:

1slow_query_log = 1 2long_query_time = 1 3slow_query_log_file = /var/log/mariadb/slow-query.log 4log_queries_not_using_indexes See details in https://www.cyberciti.biz/faq/how-to-set-and-enable-mariadb-slow-query-log-linux-unix/