MySQL
Contents
-
MySQL
- Preface
- Commercial MySQL Support
- About
- Configure MySQL Clients
- Regain Access
- Basic MySQL Setup
- Replication
- Migration MyISAM to InnoDB
- Backup and Restore
- Storage engines
- Password Hashing Algorithm
- Debian specific Notes on MySQL 5.6
- Profiling, Performance Tuning, Problem Tracing
- Some Queries
- automysqlbackup
- Tools
Preface
This digest started in 2016 and is under permanent review. There may be options that no-longer or not-yet exist in the MySQL-ish Version you use. You will need to adjust the recommendations, processes and techniques to your very own case and gusto.
Commercial MySQL Support
You may aquire support for your database-server here.
About
- The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.
- The MySQL mascot is a dolphin called "Sakila".
MySQL reference manual
- Still available as website
- Only as PDF
Percona
Configure MySQL Clients
/root/.my.cnf
This is not less secure than the default Debian configuration, because a user with root-privileges can also
utilize mysql with the option --defaults-extra-file=/etc/mysql/debian.cnf or
reconfigure mysql with the Option skip-grant-tables, restart mysqld and access without authentication.
mysql_config_editor
Regain Access
Please delete the statement that updates the passwords from your ~/.mysql_history 
1 mysql --defaults-extra-file=/etc/mysql/debian.cnf
2 Welcome to the MySQL monitor. Commands end with ; or \g.
3 Your MySQL connection id is 204
4 Server version: 5.5.46-0+deb8u1 (Debian)
5
6 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
7
8 Oracle is a registered trademark of Oracle Corporation and/or its
9 affiliates. Other names may be trademarks of their respective
10 owners.
11
12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14 mysql>
15 mysql> update mysql.user set password=PASSWORD('aSecureMySQLRootPassword') where user='root';
16 Query OK, 4 rows affected (0.02 sec)
17 Rows matched: 4 Changed: 4 Warnings: 0
18 mysql> update mysql.user set password=PASSWORD('aSecureMySQLKolabPassword') where user='kolab';
19 Query OK, 1 row affected (0.00 sec)
20 Rows matched: 1 Changed: 1 Warnings: 0
21 mysql> flush privileges;
22 Query OK, 0 rows affected (0.11 sec)
Test Access
Make sure it works, before you spend time in debugging.
1 mysql
2 Welcome to the MySQL monitor. Commands end with ; or \g.
3 Your MySQL connection id is 205
4 Server version: 5.5.46-0+deb8u1 (Debian)
5
6 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
7
8 Oracle is a registered trademark of Oracle Corporation and/or its
9 affiliates. Other names may be trademarks of their respective
10 owners.
11
12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14 mysql> Bye
15 mysql -u kolab -p
16 Enter password:
17 Welcome to the MySQL monitor. Commands end with ; or \g.
18 Your MySQL connection id is 206
19 Server version: 5.5.46-0+deb8u1 (Debian)
20
21 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
22
23 Oracle is a registered trademark of Oracle Corporation and/or its
24 affiliates. Other names may be trademarks of their respective
25 owners.
26
27 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
28
29 mysql> quit
30 Bye
Basic MySQL Setup
- There are some parameters that are important to be set, if performance matters.
- The configuration is dosini-style and will not take effect if you forget
to make sure the file-suffix is .cnf (without "o")!
- to add a group definition (like "[mysqld]") in front of your configuration
- the assignment operator "="
- Within option names, dash (-) and underscore (_) may be used interchangeably.
List of parameters to adjust
The defaults were always fine for very small databases with little demand on performance. But once they grew and needed to be fast, there had some tuning to be done. With newer versions (v5.7+) this improved a lot! New installations are almost perfectly tuned by default.
Pre mysql_install_db
datadir
default_character_set
innodb_file_per_table
innodb_log_file_size
performance_schema
tmpdir
slave_load_tmpdir
innodb_tmpdir
During tuning
innodb_log_buffer_size
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_stats_on_metadata
query_cache_limit
query_cache_size
open_files_limit
table_open_cache
slow_query_log
slow_query_log_file
long_query_time
In the end that's not too much - we can handle them.
Filesystem Layout
Get current config
1 FSLAYOUT_CNF="/etc/mysql/conf.d/fslayout.cnf"
2 echo '[mysqld]' > "$FSLAYOUT_CNF"
3 mysqld --help --verbose 2>/dev/null \
4 |grep '^\w.*dir\s' \
5 |sed -r 's/^([[:alnum:]-]*\s*)/#\1= /' >> "$FSLAYOUT_CNF"
6 ### ALTERNATIVELY
7 mysqld --help --verbose 2>/dev/null \
8 |awk ' /^\w.*dir\s/ {print "#"$1, "=" ,$2}' \
9 |column -t >> "$FSLAYOUT_CNF"
/etc/mysql/conf.d/fslayout.cnf
1 [mysqld]
2 #basedir = /usr
3 #character-sets-dir = /usr/share/mysql/charsets/
4 datadir = /var/lib/mysql/data
5 #innodb-data-home-dir = (No default value)
6 #innodb-log-group-home-dir = (No default value)
7 #innodb_tmpdir =
8 innodb_tmpdir = /var/lib/mysql/tmp/innodb
9 #lc-messages-dir = /usr/share/mysql/
10 #plugin-dir = /usr/lib/mysql/plugin/
11 #slave-load-tmpdir = /tmp
12 slave-load-tmpdir = /var/lib/mysql/tmp/slave
13 #tmpdir = /tmp
To save some disk-IO, we mount a tmpfs at /tmp.
/etc/fstab
You might need to add some restrictive options to RAM usage to make memory layout more predictable size=4g.
And in this case we also should point other temporary files directories away from the volatile directory.
#slave_load_tmpdir to have a permanent location
#innodb_tmpdir to avoid filling up the RAM
Temporary files
MySQL uses temporary files in various on ocasions.
Default configuration
1 MariaDB [(none)]> SHOW VARIABLES WHERE VARIABLE_NAME LIKE '%tmp%' OR VARIABLE_NAME LIKE '%heap%';
2 +----------------------------+----------------------+
3 | Variable_name | Value |
4 +----------------------------+----------------------+
5 | default_tmp_storage_engine | |
6 | encrypt_tmp_disk_tables | OFF |
7 | encrypt_tmp_files | OFF |
8 | innodb_tmpdir | |
9 | max_heap_table_size | 16777216 |
10 | max_tmp_tables | 32 |
11 | slave_load_tmpdir | /tmp |
12 | tmp_disk_table_size | 18446744073709551615 |
13 | tmp_memory_table_size | 16777216 |
14 | tmp_table_size | 16777216 |
15 | tmpdir | /tmp |
16 +----------------------------+----------------------+
17 11 rows in set (0.001 sec)
MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases, lsof +L1 may be helpful in identifying large files associated with mysqld.)
When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files.
tmpdir
tmpdir might reside on a non-permanent location.
mysqld_tmpdir
slave_load_tmpdir
The name of the directory where the slave creates temporary files. When the slave SQL thread replicates a LOAD DATA statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table.
slave_load_tmpdir
innodb_tmpdir
Used to define an alternate directory for temporary sort files created during online ALTER TABLE operations that rebuild the table.
innodb_tmpdir
Logging
- The general-log is a performance killer and should only be used in very seldom cases.
- The error-log can overrides the default configuration to have in one place if you like.
- Slow-query log is essential stuff for performance analysis.
/etc/mysql/conf.d/logging.cnf
1 [mysqld]
2 general-log = FALSE
3 general-log-file = /var/log/mysql/mail.log
4 #log-error = /var/log/mysql/error.log
5 #log-isam = myisam.log
6 #log-output = FILE
7 log-queries-not-using-indexes = TRUE
8 #log-raw = FALSE
9 #log-short-format = FALSE
10 log-slow-admin-statements = TRUE
11 #log-slow-slave-statements = FALSE
12 #log-tc = tc.log
13 #log-tc-size = 24576
14 #log-throttle-queries-not-using-indexes = 0
15 #log-warnings = 1
16 slow-query-log = TRUE
17 slow-query-log-file = /var/log/mysql/mail-slow.log
18 #long-query-time = 10
19
InnoDB configuration
I like generating a file /etc/mysql/conf.d/innodb.cnf, which is included at the end of /etc/mysql/my.cnf overriding the defaults, with the following commands.
1 INNODB_CNF="/etc/mysql/conf.d/innodb.cnf"
2 echo '[mysqld]' > "$INNODB_CNF"
3 mysqld --help --verbose 2>/dev/null \
4 |grep '^innodb' \
5 |sed -r 's/^([[:alnum:]-]*\s*)/#\1= /' >> "$INNODB_CNF"
6 ### ALTERNATIVELY
7 mysqld --help --verbose 2>/dev/null \
8 |awk ' /^innodb/ {print "#"$1, "=" ,$2}' \
9 |column -t >> "$INNODB_CNF"
/etc/mysql/conf.d/innodb.cnf
1 [mysqld]
2 #innodb = ON
3 #innodb-adaptive-flushing = TRUE
4 #innodb-adaptive-hash-index = TRUE
5 #innodb-additional-mem-pool-size = 8388608
6 #innodb-autoextend-increment = 8
7 #innodb-autoinc-lock-mode = 1
8 #innodb-buffer-page = ON
9 #innodb-buffer-page-lru = ON
10 ### Default 1
11 innodb-buffer-pool-instances = 1
12 ### Default 128M
13 innodb-buffer-pool-size = 256M
14 #innodb-buffer-pool-stats = ON
15 #innodb-change-buffering = all
16 #innodb-checksums = TRUE
17 #innodb-cmp = ON
18 #innodb-cmp-reset = ON
19 #innodb-cmpmem = ON
20 #innodb-cmpmem-reset = ON
21 #innodb-commit-concurrency = 0
22 #innodb-concurrency-tickets = 500
23 #innodb-data-file-path = (No default value)
24 #innodb-data-home-dir = (No default value)
25 #innodb-doublewrite = TRUE
26 #innodb-fast-shutdown = 1
27 #innodb-file-format = Antelope
28 #innodb-file-format-check = TRUE
29 #innodb-file-format-max = Antelope
30 #innodb-file-io-threads = 4
31 innodb-file-per-table = true
32 #innodb-flush-log-at-trx-commit = 1
33 #innodb-flush-method = (No default value)
34 #innodb-force-load-corrupted = FALSE
35 #innodb-force-recovery = 0
36 #innodb-io-capacity = 200
37 #innodb-large-prefix = FALSE
38 #innodb-lock-wait-timeout = 50
39 #innodb-lock-waits = ON
40 #innodb-locks = ON
41 #innodb-locks-unsafe-for-binlog = FALSE
42 #innodb-log-buffer-size = 8388608
43 ### Default 5M
44 innodb-log-file-size = 128M
45 #innodb-log-files-in-group = 2
46 #innodb-log-group-home-dir = (No default value)
47 #innodb-max-dirty-pages-pct = 75
48 #innodb-max-purge-lag = 0
49 #innodb-mirrored-log-groups = 1
50 #innodb-old-blocks-pct = 37
51 #innodb-old-blocks-time = 0
52 #innodb-open-files = 300
53 #innodb-print-all-deadlocks = FALSE
54 #innodb-purge-batch-size = 20
55 #innodb-purge-threads = 0
56 #innodb-random-read-ahead = FALSE
57 #innodb-read-ahead-threshold = 56
58 #innodb-read-io-threads = 4
59 #innodb-replication-delay = 0
60 #innodb-rollback-on-timeout = FALSE
61 #innodb-rollback-segments = 128
62 #innodb-spin-wait-delay = 6
63 #innodb-stats-method = nulls_equal
64 ### DISABLE CARDINALITY GUESS ON METADATA ACCESS
65 innodb-stats-on-metadata = FALSE
66 #innodb-stats-sample-pages = 8
67 #innodb-status-file = FALSE
68 #innodb-strict-mode = FALSE
69 #innodb-support-xa = TRUE
70 #innodb-sync-spin-loops = 30
71 #innodb-table-locks = TRUE
72 #innodb-thread-concurrency = 0
73 #innodb-thread-sleep-delay = 10000
74 #innodb-trx = ON
75 #innodb-use-native-aio = TRUE
76 #innodb-use-sys-malloc = TRUE
77 #innodb-write-io-threads = 4
innodb_buffer_pool
Sizing
innodb_buffer_pool_size
Is always to be seen in conjunction with #innodb_buffer_pool_instances and #innodb_buffer_pool_chunk_size
- The larger, the less disk-IO.
- Default: 128MB
innodb_buffer_pool_instances
With large buffer-pools you can optimize concurrency by dividing the pool into regions. Each chunk should be at least 1GiB in size. This option only takes effect if the buffer-pool is bigger than 1GiB. The default value is 1 or 8, min. value is 1, max. value is 64.
innodb_buffer_pool_chunk_size
Default: 128MiB
Size of memory operations that MySQL performs, e.g. when changing innodb_buffer_pool_size.
Reduce Warmup
Introduced: MySQL 5.6+ Default: ON (5.7+), OFF (5.6)
innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_pct
Specifies the percentage of the most recently used pages for each buffer pool to read out and dump.
The range is 1 to 100. The default value is 25.
For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.
innodb_buffer_pool_filename
Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is named ib_buffer_pool and is located in the InnoDB data directory. A non-default location must be specified relative to the data directory.
innodb_log
innodb_log_buffer
innodb_log_buffer_size
- Helps with large transactions: The larger, the less disk-io and better performance.
- Default: 16MiB
innodb_log_file
One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer memory area.
By default located in datadir: ib_logfile0, ib_logfile1
Sum of required diskspace is #innodb_log_file_size * #innodb_log_files_in_group
innodb_log_file_size
- There should be enough redo log space to handle more than an hour of write activity.
- Default: 48MiB
innodb_log_files_in_group
- Log files are rotated.
- Default: 2 (recommmended)
Initializing MySQL
Prior to executing these steps you should consider creating a #Backup with mysqldump
Cleanup/purge anything or move it away (as you like and have the resources)
Initialization
1 install -o mysql -g mysql -d \
2 /var/lib/mysql/{data,relay_logs,bin_logs,tmp/{slave,innodb}}
3 mysql_install_db
4 Installing MySQL system tables...
5 160109 0:19:55 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1) starting as process 5043 ...
6
7 OK
8 Filling help tables...
9 160109 0:19:57 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1) starting as process 5049 ...
10 OK
11
12 To start mysqld at boot time you have to copy
13 support-files/mysql.server to the right place for your system
14
15 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
16 To do so, start the server, then issue the following commands:
17
18 /usr/bin/mysqladmin -u root password 'new-password'
19 /usr/bin/mysqladmin -u root -h mail password 'new-password'
20
21 Alternatively you can run:
22 /usr/bin/mysql_secure_installation
23
24 which will also give you the option of removing the test
25 databases and anonymous user created by default. This is
26 strongly recommended for production servers.
27
28 See the manual for more instructions.
29
30 You can start the MySQL daemon with:
31 cd /usr ; /usr/bin/mysqld_safe &
32
33 You can test the MySQL daemon with mysql-test-run.pl
34 cd /usr/mysql-test ; perl mysql-test-run.pl
35
36 Please report any problems at http://bugs.mysql.com/
Open a tmux session, the log (following) and start the MySQL-Daemon.
If you want to restore the complete data see #Restore with mysqldump Otherwise on debian you should create a user according to your /etc/mysql/debian.cnf and grant him the appropriate privileges.
1 GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>';
Timezone Configuration
- Useful to make timezone conversions (e.g. to UTC)
- Import timezone data into MySQL-Tables.
1 # mysql_tzinfo_to_sql
2 Usage:
3 mysql_tzinfo_to_sql timezonedir
4 mysql_tzinfo_to_sql timezonefile timezonename
5 mysql_tzinfo_to_sql --leap timezonefile
6 # mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql
7 Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
8 Warning: Unable to load '/usr/share/zoneinfo//leap-seconds.list' as time zone. Skipping it.
9 Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
10 Warning: Unable to load '/usr/share/zoneinfo//zone1970.tab' as time zone. Skipping it.
Index Statistics Maintenance
It is difficult for mysql to determine the cardinality of a column index reliably. The technique used to guess the cardinality is called "random dives". innodb_stats_sample_pages (default 8) random index pages are screened and a distinct values for the whole table is extrapolated. The amount of data to be read (e.g. from disk) therefore depends on innodb_page_size, which is 16KiB by default.
The MySQL-Query-Optimzer then uses these gathered statistics to plan the execution. But the cardinality can be guessed terribly wrong because the content of the random pages can differ severely and finally leads to poor query performance. Better statistics can be gathered by increasing innodb_stats_sample_pages to a certain point under the cost of some IO.
Statistics are also gathered with ANALYSE TABLE … or mysqlanalyse.
Prior to mysql 5.6.6 statistics were not permanent innodb_stats_persistent and renewed on meta data access innodb-stats-on-metadata.
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_persistent
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html
Query Cache
Some info
Notes
The query cache have been removed in MySQL 8.
- Always, adjust it according to the numbers. It strictly depends on the workload.
- Also consider disabling it (might win up to 13%).
- Updates/Inserts of a table invalidate the cached queries of that table.
- Better with a reading workload.
General optimizations
- Batched writes
- Many small tables
- don't make it too big
Optimization process
- Cache hit-ratio good?
- Cacheable (DERMINISTIC? (RAND), LIMIT, …) ?
- Fragmentation?
- many low memory prunes?
- Write heavy workload?
/etc/mysql/conf.d/query-cache.cnf
Monitoring
Tuning
/etc/mysql/conf.d/tuning.cnf
Schemas
Performance Schema
Prior to 5.6 performance schema is disabled by default.
Sys Schema
A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.
Optional Sysschema installation for MySQL 5.6 Sysschema is installed by default under MySQL 5.7 and MySQL 8 from Oracle. By default, on MySQL 5.6/5.7/8, performance schema is enabled by default. For previous 5.6 version, you can follow this command to create a new database sys containing very useful view on Performance schema
Encoding
Display default encoding
1 mysql> show variables like 'char%';
2 +--------------------------+----------------------------+
3 | Variable_name | Value |
4 +--------------------------+----------------------------+
5 | character_set_client | utf8 |
6 | character_set_connection | utf8 |
7 | character_set_database | utf8 |
8 | character_set_filesystem | binary |
9 | character_set_results | utf8 |
10 | character_set_server | utf8 |
11 | character_set_system | utf8 |
12 | character_sets_dir | /usr/share/mysql/charsets/ |
13 +--------------------------+----------------------------+
14 8 rows in set (0.00 sec)
utf8 is currently an alias for utf8mb3
utf8 ≢ UTF-8
You should probably use: utf8mb4
- utf8mb3 uses max. 3 byte for a character and is a subset of utf8mb4.
- utf8mb4 uses max. 4 byte for a character
To set the default character encoding to utf8mb4 add the following lines to your my.cnf and restart the server.
Converting the tables later may be a rattle.
Replication
There are several replication methods
Simple MySQL internal replication
- Statement based replication
- Rowbased based replication (default since 5.1)
Multi-master MySQL internal replication
- 2-master replication
- Circular replication
- MySQL NDB-Cluster (Network Database)
- not capable of XA-transactions
- Galera Cluster replication - to be examined - maybe later more
- Mariadb multi-source replication
- Percona multi-source replication
MySQL internal replication
- Official docs from the mysql reference manual:
- every slave can only have one master (replication source)
- with the blackhole-storage engine a multi-source replication can be simulated
- as long as you don't use "ALTER TABLE"
still a linear topology M1 (db1) -> M2 (db1(bh), db2) -> S1 (db1,db2)
- with the blackhole-storage engine a multi-source replication can be simulated
- single threaded until MySQL 5.6
Simple MySQL internal replication
- basic build-block to implement non-invasive backups
Multi-master MySQL internal replication
- enables the possibility of read-scaling
- no write-scaling, since binary log has to be applied to slaves as well
- limited capability to attach additional masters depending on the chosen row-increment
Migration MyISAM to InnoDB
Migrate any table with Engine MyISAM (with exception of schemas "information_schema" and "mysql") to InnoDB.
Fast coded script that does the job.
alter_engine.sh
1 #!/bin/bash
2
3 DATABASES="$(mysql -Nrse 'show databases;')"
4 for DATABASE in $DATABASES; do
5
6 if (grep -qv -e information_schema -e mysql <<< "$DATABASE"); then
7 TABLES="$(mysql -Nrse "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = '$DATABASE' and ENGINE = 'MyISAM';")"
8
9 for TABLE in $TABLES; do
10 echo "Processing TABLE '$DATABASE.$TABLE'"
11 mysql -re "ALTER TABLE $DATABASE.$TABLE ENGINE=InnoDB;"
12 done
13 else
14 echo "Skipping database '$DATABASE'"
15 continue
16 fi
17 done
Untested version with less spaghetti-style. alter_engine.sh
1 #!/bin/bash
2
3 DATABASES="$(mysql -Nrse 'show databases;')"
4 for DATABASE in $DATABASES; do
5 if (grep -qv -e information_schema -e mysql <<< "$DATABASE"); then
6 echo -e "Processing Database '$DATABASE'"
7 TABLES="$(mysql -Nrs <<-EOH
8 SELECT TABLE_NAME FROM information_schema.TABLES
9 WHERE TABLE_SCHEMA = '$DATABASE'
10 AND ENGINE = 'MyISAM';")"
11 EOH
12 for TABLE in $TABLES; do
13 echo -e "\nProcessing TABLE '$DATABASE.$TABLE'"
14 mysql -re "ALTER TABLE $DATABASE.$TABLE ENGINE=InnoDB;"
15 done
16 else
17 echo -e "Database '$DATABASE' is on blacklist - skipping …\n"
18 continue
19 fi
20 done
Backup and Restore
Please keep in mind that backup is only valid if you can restore it!
- You'll probably need binary and statement-based/SQL backup of your databases.
- A binary backup is quickly taken and restored.
- A SQL backup of your data is much smaller and may may be restored elsewhere.
Offline
Copy with rsync
Create Initial Backup
For some experiments it is useful to keep a binary copy of the initial setup, that can be restored in a fraction of a second.
Restore Initial Backup
To avoid running mysql_install_db, mysql_secure_installation just do the following
Online
Please try
mydumper/myloader - since it is multi-threaded
xtrabackup - Percona-Tools since it is optimized to be non-invasive
mysqldump - since it's available everywhere
Nice additions
pigz (Parallel Implementation of GZip) is so much faster than gzip on multicore systems and compatible to gzip
Deprecated
- mysqlhotcopy - a perl-script that can backup MyISAM and Archive-Tables
mysqldump
- SQL statement based
- Available anywhere.
- doesn't scale too well
- single-treaded and therefore slow as hell
- Take care of the order of the parameters. An option that was disabled may be enabled implicitly later.
Backup with mysqldump
Prepare a destination
Create a backup of your data, where all tables are transactional InnoDB.
Create a backup of your data, where non-transactional tables have to be backed up and need to be consistent to each other.
Notes
Enabled by default mysqldump --print-defaults
--opt equals to
--add-drop-table --add-locks --create-options --disable-keys \ --extended-insert --lock-tables --quick --set-charset
--triggers
--quick
--quote-names
--max_allowed_packet=16M
--default-character-set=utf8mb4
The option --master-data=2 ensures that the backup contains the master binary log coordinates as a comment (=2).
- Disable it if you don't have replication or binary logs or you'll get
mysqldump: Error: Binlogging on server not active
- Disable it if you don't have replication or binary logs or you'll get
--routines is not necessary if mysql.proc is dumped and restored. This option does not preserve time of creation or creator of a routine.
Make sure the backup has been created successfully.
1 zcat /var/backups/mysql_backup_2016-01-06.sql.gz |head
2 -- MySQL dump 10.13 Distrib 5.5.46, for debian-linux-gnu (x86_64)
3 --
4 -- Host: localhost Database:
5 -- ------------------------------------------------------
6 -- Server version 5.5.46-0+deb8u1
7
8 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
10 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
11 /*!40101 SET NAMES utf8 */;
Restore with mysqldump
Preparation
- Make sure there is enough storage space.
- Cleanup old data
- You may want to disable foreign-key constraints to speed up the restore.
During the restore
- Make sure binary logs do not consume all storage space, while taking care of you slaves
1 # pigz -dc < /var/backups/mysql_backup_2016-01-06.sql.gz |mysql -p
2 Enter password:
3 # mysql
4 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
5 # mysql -p
6 Enter password:
7 Welcome to the MySQL monitor. Commands end with ; or \g.
8 Your MySQL connection id is 18
9 Server version: 5.5.46-0+deb8u1 (Debian)
10
11 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
12
13 Oracle is a registered trademark of Oracle Corporation and/or its
14 affiliates. Other names may be trademarks of their respective
15 owners.
16
17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18
19 mysql> flush privileges;
20 Query OK, 0 rows affected (0.00 sec)
21
22 mysql> Bye
23 mysql -p
24 Enter password:
25 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
26 # mysql
27 Welcome to the MySQL monitor. Commands end with ; or \g.
28 Your MySQL connection id is 20
29 Server version: 5.5.46-0+deb8u1 (Debian)
30
31 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
32
33 Oracle is a registered trademark of Oracle Corporation and/or its
34 affiliates. Other names may be trademarks of their respective
35 owners.
36
37 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
38
39 mysql>
mydumper
- SQL statement based
- multi-threaded and therefore fast
Backup with mydumper
Dump anything!
1 DIR_BASE="/var/backups/mydumper"
2 install -o root -g root -m 0750 -d "$DIR_BASE"
3 cd "$DIR_BASE"
4 DIR_OUT="$DIR_BASE/export_$(date --utc +%F_%T_%Z)"
5 mydumper \
6 --defaults-file "/etc/mysql/debian.cnf" \
7 --outputdir "/var/backups/mydumper/$DIR_OUT" \
8 --threads "$(($(nproc)/2))" \
9 --triggers --events --routines --binlogs \
10 --logfile "/var/backups/mydumper/$DIR_OUT/mydumper.log" \
11 --use-savepoints --compress --tz-utc --verbose 2
12
13 ### OR COPRESS MANUALLY AFTERWARDS
14 for FILE in "$DIR_OUT"/*; do
15 pigz "$FILE"
16 done
If you are replicating you should backup the binlogs, too: --binlogs.
You'll find the master position in a file .metadata in the output directory.
If you are backing up a remote server you may want to add --compress-input to save some traffic in trade for some cpu-cycles.
Restore with myloader
Restore time depends much on your largest table.
Interesting options
--overwrite-tables
--source-db SOURCE_DB --database DESTINATION_DB
xtrabackup
Percona XtraBackup is an open-source hot backup utility for MySQL-based servers that doesn’t lock your database during the backup.
The ondisk format has significantly diverged between mysql/percona and mariadb since the dork. Since MariaDB 10.1 a own tool based on percona-xtrabackup 2.3 must be used to backup the MariaDB
'''mariadb-backup'''.
So i guess, options a are interchangeable between percona-xtrabackup and mariadb-backup. So just exchange xtrabackup with mariabackup.
Features
- binary backup
- multi-threaded and therefore fast
- Point-In-Time-Recovery (PITR)
- non-invasive hot
incremental (based on log sequence number (LSN)) -> even faster
- read database config (linked against libmysql-client)
Install xtrabackup
apt install percona-xtrabackup-80 }}}
Backup-user needs privilege
BACKUP_ADMIN for LOCK TABLES FOR BACKUP or LOCK INSTANCE FOR BACKUP to block all DDL operations
PROCESS, SUPER to kill long queries
Check backup privileges
1 xtrabackup --backup --check-privileges
2 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp
3 xtrabackup: recognized client arguments: --socket=/var/run/mysqld/mysqld.sock --backup=1 --check-privileges=1
4 xtrabackup version 8.0.12 based on MySQL server 8.0.20 Linux (x86_64) (revision id: 01cce6d)
5 200604 22:26:07 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' (using password: NO).
6 200604 22:26:07 version_check Connected to MySQL server
7 200604 22:26:07 version_check Executing a version check against the server...
8
9 # A software update is available:
10 200604 22:26:08 version_check Done.
11 200604 22:26:08 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /var/run/mysqld/mysqld.sock
12 Error: Unsupported server version: '10.3.22-MariaDB-1'.
13 This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0
14 Please use Percona XtraBackup 2.4 for this database.
xtrabackup defaults
xtrabackup --print-defaults
1 xtrabackup would have been started with the following arguments:
2 --user=mysql \
3 --pid-file=/run/mysqld/mysqld.pid \
4 --socket=/run/mysqld/mysqld.sock \
5 --basedir=/usr \
6 --datadir=/var/lib/mysql \
7 --tmpdir=/tmp \
8 --lc-messages-dir=/usr/share/mysql \
9 --bind-address=127.0.0.1 \
10 --query_cache_size=16M \
11 --log_error=/var/log/mysql/error.log \
12 --expire_logs_days=10 \
13 --character-set-server=utf8mb4 \
14 --collation-server=utf8mb4_general_ci
Backup with xtrabackup
Full backup
Create a incremental backup based on full backup
1 BACKUP2="$(date +%F_%T)"
2 OPTIONS="--compress --compress-threads=4 --parallel=4 --slave-info"
3 xtrabackup --backup $OPTIONS \
4 --target-dir=/var/backups/xtrabackup/incr/"$BACKUP2" \
5 --incremental-basedir=/var/backups/xtrabackup/full/"$BACKUP1" \
6 2> /var/backups/xtrabackup/full/"$BACKUP2"/"$BACKUP2".log
Create a incremental backup based on first incremental
1 BACKUP3="$(date +%F_%T)"
2 OPTIONS="--compress --compress-threads=4 --parallel=4 --slave-info"
3 xtrabackup --backup $OPTIONS \
4 --target-dir=/var/backups/xtrabackup/incr/"$BACKUP3" \
5 --incremental-basedir=/var/backups/xtrabackup/incr/"$BACKUP2" \
6 2> /var/backups/xtrabackup/full/"$BACKUP3"/"$BACKUP3".log
Prepare restore with xtrabackup
Apply redo-log and delta files against (base-)backup.
Create a working copy of your backup to leave the "original" untouched.
With a base backup and a intermediate incremental backup you must skip the rollback of uncommitted transactions when preparing a backup --apply-log-only, because transactions that were uncommitted at the time of your backup may be in progress, and it is likely that they will be committed in the next incremental backup.
Prepare base full backup (without rollback)
Prepare intermediate incremental backup (without rollback)
Prepare final incremental backup (with rollback)
Restore with xtrabackup
mysqlhotcopy
Storage engines
The InnoDB Storage Engine is default since 5.1
But there are several Alternative Storage Engines built in.
1 mysql -e 'SHOW STORAGE ENGINES\G'
2 *************************** 1. row ***************************
3 Engine: FEDERATED
4 Support: NO
5 Comment: Federated MySQL storage engine
6 Transactions: NULL
7 XA: NULL
8 Savepoints: NULL
9 *************************** 2. row ***************************
10 Engine: CSV
11 Support: YES
12 Comment: CSV storage engine
13 Transactions: NO
14 XA: NO
15 Savepoints: NO
16 *************************** 3. row ***************************
17 Engine: MEMORY
18 Support: YES
19 Comment: Hash based, stored in memory, useful for temporary tables
20 Transactions: NO
21 XA: NO
22 Savepoints: NO
23 *************************** 4. row ***************************
24 Engine: BLACKHOLE
25 Support: YES
26 Comment: /dev/null storage engine (anything you write to it disappears)
27 Transactions: NO
28 XA: NO
29 Savepoints: NO
30 *************************** 5. row ***************************
31 Engine: MyISAM
32 Support: YES
33 Comment: MyISAM storage engine
34 Transactions: NO
35 XA: NO
36 Savepoints: NO
37 *************************** 6. row ***************************
38 Engine: MRG_MYISAM
39 Support: YES
40 Comment: Collection of identical MyISAM tables
41 Transactions: NO
42 XA: NO
43 Savepoints: NO
44 *************************** 7. row ***************************
45 Engine: ARCHIVE
46 Support: YES
47 Comment: Archive storage engine
48 Transactions: NO
49 XA: NO
50 Savepoints: NO
51 *************************** 8. row ***************************
52 Engine: InnoDB
53 Support: DEFAULT
54 Comment: Supports transactions, row-level locking, and foreign keys
55 Transactions: YES
56 XA: YES
57 Savepoints: YES
58 *************************** 9. row ***************************
59 Engine: PERFORMANCE_SCHEMA
60 Support: YES
61 Comment: Performance Schema
62 Transactions: NO
63 XA: NO
64 Savepoints: NO
Password Hashing Algorithm
The functionPASSWORD() has been deprecated, but the hashes are still around. Here's a way to generate these hashes using mysql internal methods.
1 mysql> select CONCAT('*',UPPER(SHA1(UNHEX(SHA1('test')))));
2 +----------------------------------------------+
3 | CONCAT('*',UPPER(SHA1(UNHEX(SHA1('test'))))) |
4 +----------------------------------------------+
5 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
6 +----------------------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select PASSWORD('test');
10 +-------------------------------------------+
11 | PASSWORD('test') |
12 +-------------------------------------------+
13 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
14 +-------------------------------------------+
15 1 row in set (0.00 sec)
As a hint there might by systems like Plesk, that store their passwords something like this. cat /etc/psa/.psa.shadow
1 $AES-128-CBC$ssdfsdfsdfsdf==$kilwoihkjlnfvkilje==
But the string itself, the content if the file, is the password, No, it's not decrypted as the following command proves.
Debian specific Notes on MySQL 5.6
Systemd Changes
Start with systemd broken since /usr/share/mysql/mysql-systemd-start is not configurable and so doesn't honor f.e. datadir.
- The sanity checks fail if eather
/etc/mysql/my.cnf, /var/lib/mysql/ or /var/lib/mysql/mysql is missing. Nothing is logged to /var/log/mysql/error.log, instead you can find the following in /var/log/daemon.log.
1 Jan 26 10:41:35 mail systemd[1]: Starting MySQL Community Server... 2 Jan 26 10:41:35 mail mysql-systemd-start[19395]: MySQL system database not found. Please run mysql_install_db tool. 3 Jan 26 10:41:35 mail systemd[1]: mysql.service: Control process exited, code=exited status=1 4 Jan 26 10:41:35 mail systemd[1]: Failed to start MySQL Community Server. 5 Jan 26 10:41:35 mail systemd[1]: mysql.service: Unit entered failed state. 6 Jan 26 10:41:35 mail systemd[1]: mysql.service: Failed with result 'exit-code'. 7 Jan 26 10:41:36 mail systemd[1]: mysql.service: Service hold-off time over, scheduling restart. 8 Jan 26 10:41:36 mail systemd[1]: Stopped MySQL Community Server.
As a workaround create a sym link in the specific place
1 ln -s /var/lib/mysql/data/mysql /var/lib/mysql/mysql
Configuration Layout
mysql-server-5.6 introduces a new configuration layout
1 root@mail /etc/mysql # ll
2 insgesamt 16
3 drwxr-xr-x 2 root root 6 Jan 26 10:27 conf.d/
4 -rw------- 1 root root 333 Jul 27 2014 debian.cnf
5 -rwxr-xr-x 1 root root 1193 Jan 15 15:15 debian-start*
6 lrwxrwxrwx 1 root root 24 Jan 26 10:08 my.cnf -> /etc/alternatives/my.cnf
7 -rw-r--r-- 1 root root 839 Dez 20 15:16 my.cnf.fallback
8 -rw-r--r-- 1 root root 682 Dez 20 15:16 mysql.cnf
9 drwxr-xr-x 2 root root 147 Jan 26 10:52 mysql.conf.d/
my.cnf is now a sym-link that can be administered using Debian-Alternatives
1 root@mail /etc/mysql # update-alternatives --list my.cnf
2 /etc/mysql/my.cnf.fallback
3 /etc/mysql/mysql.cnf
4 root@mail /etc/mysql # update-alternatives --config my.cnf
5 Es gibt 2 Auswahlmöglichkeiten für die Alternative my.cnf (welche /etc/mysql/my.cnf bereitstellen).
6
7 Auswahl Pfad Priorität Status
8 ------------------------------------------------------------
9 * 0 /etc/mysql/mysql.cnf 200 automatischer Modus
10 1 /etc/mysql/my.cnf.fallback 100 manueller Modus
11 2 /etc/mysql/mysql.cnf 200 manueller Modus
12
13 Drücken Sie die Eingabetaste, um die aktuelle Wahl[*] beizubehalten,
14 oder geben Sie die Auswahlnummer ein: 0
The new configuration file mysql.cnf contains two include dirs, unfortunately in the wrong order.
1 root@mail /etc/mysql # cat mysql.cnf
2 #
3 # The MySQL database server configuration file.
4 #
5 # You can copy this to one of:
6 # - "/etc/mysql/my.cnf" to set global options,
7 # - "~/.my.cnf" to set user-specific options.
8 #
9 # One can use all long options that the program supports.
10 # Run program with --help to get a list of available options and with
11 # --print-defaults to see which it would actually understand and use.
12 #
13 # For explanations see
14 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
15
16 #
17 # * IMPORTANT: Additional settings that can override those from this file!
18 # The files must end with '.cnf', otherwise they'll be ignored.
19 #
20
21 !includedir /etc/mysql/conf.d/
22 !includedir /etc/mysql/mysql.conf.d/
/etc/mysql/conf.d is no longer able to override the default configuration file because it resides in /etc/mysql/mysql.conf.d, which is read later in the course.
In /etc/mysql/mysql.conf.d an alphanumerical order is of importance. And the default configuration file is in the middle -> /etc/mysql/mysql.conf.d/mysql.cnf. We sopuld move it to the start to allow overrides but there will be conflicts during updates.
New configuration layout apprears to be broken.
Profiling, Performance Tuning, Problem Tracing
mysqltuner.pl
Great tool to identify problems and bottlenecks !1!
Star it on Github
https://github.com/major/MySQLTuner-perl
It's already included in the distributions
1 aptitude install mysqltuner
Or download it like this
root@mail /var/lib/mysql # wget -O mysqltuner.pl 'http://mysqltuner.pl' --2016-01-09 00:37:17-- http://mysqltuner.pl/ Auflösen des Hostnamen »mysqltuner.pl (mysqltuner.pl)«... 217.70.184.38 Verbindungsaufbau zu mysqltuner.pl (mysqltuner.pl)|217.70.184.38|:80... verbunden. HTTP-Anforderung gesendet, warte auf Antwort... 301 Moved Permanently Platz: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl[folge] --2016-01-09 00:37:18-- https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl Auflösen des Hostnamen »raw.github.com (raw.github.com)«... 23.235.43.133 Verbindungsaufbau zu raw.github.com (raw.github.com)|23.235.43.133|:443... verbunden. HTTP-Anforderung gesendet, warte auf Antwort... 301 Moved Permanently Platz: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl[folge] --2016-01-09 00:37:18-- https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl Auflösen des Hostnamen »raw.githubusercontent.com (raw.githubusercontent.com)«... 23.235.43.133 Verbindungsaufbau zu raw.githubusercontent.com (raw.githubusercontent.com)|23.235.43.133|:443... verbunden. HTTP-Anforderung gesendet, warte auf Antwort... 200 OK Länge: 108419 (106K) [text/plain] In »»mysqltuner.pl«« speichern. mysqltuner.pl 100%[==========================================================================================>] 105,88K --.-KB/s in 0,02s 2016-01-09 00:37:19 (4,50 MB/s) - »»mysqltuner.pl«« gespeichert [108419/108419]
Invoke mysqltuner with perl as interpreter. You may force a colored output by adding thze option --color.
root@mail /var/lib/mysql # perl mysqltuner.pl
[OK] Logged in using credentials from debian maintenance account.
>> MySQLTuner 1.6.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0+deb8u1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 1008K (Tables: 33)
[!!] Total fragmented tables: 33
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'roundcube@%' has user name as password.
[!!] User 'roundcube@localhost' has user name as password.
[!!] User 'roundcube@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations -------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 21m 53s (568 q [0.067 qps], 26 conn, TX: 22K, RX: 599K)
[--] Reads / Writes: 46% / 54%
[--] Binary logging is disabled
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum reached memory usage: 194.7M (10.47% of installed RAM)
[OK] Maximum possible memory usage: 597.8M (32.15% of installed RAM)
[OK] Slow queries: 0% (0/568)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 57.69% (15/26)
[!!] Query cache efficiency: 0.0% (0 cached / 13 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 3 total)
[OK] Thread cache hit rate: 96% (1 created / 26 connections)
[OK] Table cache hit rate: 26% (25 open / 96 opened)
[OK] Open file limit used: 1% (16/1K)
[OK] Table locks acquired immediately: 100% (99 immediate / 99 locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[OK] Read Key buffer hit rate: 100.0% (6K cached / 0 reads)
[OK] Write Key buffer hit rate: 98.2% (3K cached / 61 writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/1008.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 5.05% (414 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (6508 hits/ 6508 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 46 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connections and network issues
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)Maximum output
1 mysqltuner \
2 --buffers --dbstat --idxstat --sysstat --pfstat --tbstat \
3 --outputfile mysqltuner_$(date +%F).txt
4
5 ### OR SIMPLY
6 mysqltuner -v --outputfile mysqltuner_$(date +%F).txt
7
8 ### OR WRITE THE OUTPUT TO DIFFERENT FILES
9 for PARAM in buffers dbstat idxstat sysstat pfstat tbstat; do
10 mysqltuner "--$PARAM" --outputfile "mysqltuner_$(date +%F)_$PARAM.txt"
11 done
Moderate/optimal output
- Table Column Metrics (--tbstat) are to much for me and will probably contain sensitive data
sysbench
1 apt install sysbench
Compiled-in tests
Test defined via lua in /usr/share/sysbench
Prepare setup
Prepare MyISAM, TRX=no
Defaults:
- 10min
- 1 thread
Run MyISAM, TRX=no, THREADS=1,2,4
percona-tools
Configure Percona repository
Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html
lsb_release determines sid in my case … :-/
Install percona-toolkit
Percona Toolkit is a collection of advanced command-line tools used by Percona (http://www.percona.com/) support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually.
These tools are ideal alternatives to private or "one-off" scripts because they are professionally developed, formally tested, and fully documented. They are also fully self-contained, so installation is quick and easy and no libraries are installed.
Your enterprise DBAs oversee one of your most valuable assets: the database. They need to plan, design, and deploy new architectures to meet the needs of your applications. They can’t do that if they are busy with routine tasks.
1 apt install percona-toolkit
pt-query-digest
Analyze MySQL queries from logs, processlist, and tcpdump.
Analyse slow query log
1 pt-query-digest slow.log
mysqloptimize
1 mysqloptimize --all-databases
mytop
Some Queries
Storage consumption
Size of data stored in different storage engines
Size of all tables in all databases
Maximum memory consumption
Maximum memory consumption - MySQL documentation
This formula is from Memory Configuration
This is the same formula as a SQL-Query.
I guess this is far from perfection but it's a starting point. I'll take a look on mysqltuner and determine the maximum memory is calculated by this project. Once i'm done, i'll publish an adjusted formula and query.
Maximum memory consumption - mysqltuner
This is derived from mysqltuner
1 SET @buffers_per_thread =
2 @@read_buffer_size
3 + @@read_rnd_buffer_size
4 + @@sort_buffer_size
5 + @@thread_stack
6 + @@join_buffer_size;
7
8 SET @total_buffers_per_thread =
9 @buffers_per_thread * @@max_connections;
10
11 SET @max_temp_table_size =
12 GREATEST(
13 @@tmp_table_size,
14 @@max_heap_table_size);
15
16 SET @innodb_additional_mem_pool_size = (
17 SELECT CASE
18 EXISTS(
19 SELECT GLOBAL_VALUE
20 FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES
21 WHERE VARIABLE_NAME = 'innodb_additional_mem_pool_size')
22 WHEN 1 THEN (
23 SELECT GLOBAL_VALUE
24 FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES
25 WHERE VARIABLE_NAME = 'innodb_additional_mem_pool_size')
26 ELSE 0
27 END as "innodb_additional_mem_pool_size");
28
29 SET @aria_pagecache_buffer_size = (
30 SELECT CASE
31 EXISTS(
32 SELECT GLOBAL_VALUE
33 FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES
34 WHERE VARIABLE_NAME = 'aria_pagecache_buffer_size')
35 WHEN 1 THEN (
36 SELECT GLOBAL_VALUE
37 FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES
38 WHERE VARIABLE_NAME = 'aria_pagecache_buffer_size')
39 ELSE 0
40 END as "aria_pagecache_buffer_size");
41
42 SET @server_buffers =
43 @max_temp_table_size
44 + @innodb_additional_mem_pool_size
45 + @aria_pagecache_buffer_size
46 + @@key_buffer_size
47 + @@innodb_buffer_pool_size
48 + @@innodb_log_buffer_size
49 + @@query_cache_size;
50
51 ### OUTPUT
52 SELECT @buffers_per_thread
53 / POWER(2,20) as "Buffers per thread (MiB)";
54
55 SELECT @total_buffers_per_thread
56 / POWER(2,20) as "Total buffers per thread (MiB)";
57
58 SELECT @server_buffers
59 / POWER(2,20) as "Server Buffers (MiB)";
I still think this is not a perfect calculation, but it's better than the first naive one.
Still improving …
Extract DDL of a table or database
DDL of single table with SHOW
1 SHOW CREATE TABLE $DBNAME.$TBLNAME;
Whole schema with mysqldump (there is also --all-databases)
1 mysqldump --no-data $DBNAME
Get indices
Indices of single table with SHOW
1 SHOW INDEX FROM $DBNAME.$TBLNAME;
Get all indexes from all schemas
automysqlbackup
* Single threaded via mysqldump, but customizable
Install
1 apt install automysqlbackup
Configuration is at /etc/default/automysqlbackup
create a mount point to save mysql-backups via /etc/fstab or autofs
- enable post backup script
Script deletes old backups and syncs the local backup remote. /etc/mysql-backup-post
1 #!/bin/bash
2 DEST_LOCAL="/var/lib/automysqlbackup"
3 DEST_REMOTE="/path/to/nfs/backup/$HOSTNAME"
4
5 [ -d "$DEST_LOCAL" ] || exit 1
6 [ -d "$DEST_REMOTE" ] || exit 1
7
8 find "$DEST_LOCAL/daily" -type f -ctime +7 -delete
9 find "$DEST_LOCAL/weekly" -type f -ctime +$((4*7)) -delete
10 find "$DEST_LOCAL/monthly" -type f -ctime +$((2*5*7)) -delete
11
12 rsync -a "$DEST_LOCAL" "$DEST_REMOTE"
Tools
mysql_config_editor
The mysql_config_editor utility enables you to store authentication credentials in an obfuscated login path file named .mylogin.cnf. The file location is the %APPDATA%\MySQL directory on Windows and the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.
To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is recognized by mysql_config_editor, by standard MySQL clients (mysql, mysqladmin, and so forth), and by the mysql-test-run.pl testing utility.
Show content of a login path file
1 mysql_config_editor print --all