MySQL Tricks & Tools
Dump tables with wildcard
Mysqldump wildcards? You can pass -N to mysql and you won’t need to run the grep to filter out the Tables_in row.
MySQL: Dump tables with wildcard
Truncate all tables / Drop all tables
Truncate tables:
mysql -e "set foreign_key_checks = 0;" dbname
mysql -Nse 'show tables' dbname | while read t; do mysql -e "set foreign_key_checks = 0; truncate table $t" dbname; done
mysql -e "set foreign_key_checks = 1;" dbname
Create a user
– – database dump source
CREATE USER ‘dgvip’@’%’ IDENTIFIED BY ‘45c1ce4de1b30057cd32c476e552622cd57e5de3’;
GRANT USAGE ON datingvip\_*
.* TO ‘dgvip’@’%’ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON datingvip\_%
.* TO ‘dgvip’@’%’;
GRANT SUPER ON . TO ‘dgvip’@’%’;
Secure mysql (default procedures)
mysql_secure_installation
And check tuning parameters:
cd /usr/local/src/
wget http://day32.com/MySQL/tuning-primer.sh
chmod u+x tuning-primer.sh
./tuning-primer.sh
Now, that’s on other repo:
BMDan/tuning-primer.sh: MySQL Tuning-Primer.sh, updated and improved and run: curl -L https://raw.githubusercontent.com/BMDan/tuning-primer.sh/main/tuning-primer.sh | bash
Free MySQL Hosting (testing)
db4free.net - get a MySQL 5.6 Database for free mysql –user=devbox –password=devbox –host=db4free.net
Big random data to pull from (2Mbps upload speed): Public MySQL Server mysql -uanonymous -hensembldb.ensembl.org
Dump everything in all databases
mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql
Reset data directory
MySQL: Reducing ibdata1 | Valery’s Mlog
grep datadir /etc/mysql/my.cnf
cd /var/lib
mv mysql/ mysql-backup
mkdir mysql
chmod mysql --reference mysql-backup/
chown mysql --reference mysql-backup/
# Set anything you like in /etc/mysql/my.cnf
mysql_install_db
service mysql start
Important my.cnf settings
/etc/mysql/conf.d/mysettings.cnf
echo ' [mysqld] innodb_file_per_table ’ > /etc/mysql/conf.d/mysettings.cnf
Ok. I finished. There is a lot of settings.
Install latest MySQL from PPA
I had to do this at Hetzner, on my Experiment VM.
apt-get install python-software-properties
add-apt-repository ppa:nathan-renniewaldock/ppa
apt-get update
apt-get install mysql-server
-
mysql –host=utpc-office.dyndns-server.com –user=dgvip –password=45c1ce4de1b30057cd32c476e552622cd57e5de3 -e “show variables;” > office-mysql-setting.txt
mysql –host=savioko.com –user=dgvip –password=45c1ce4de1b30057cd32c476e552622cd57e5de3 -e “show variables;”
Bash oneliners
Unpack every archive in directory (they will be removed)
ls *.bz2 | xargs -i bunzip2 {}
Sort by size in reverse, list the smallest 27, remove extension, and process
ls -Sr *.sql | head -n 27 | sed s/\.sql// | xargs -i ./mysqlrestore_sql.sh {}
-
MySQL Helper function
printf “%s\n%s”
‘my() { p=$1; if [ “${p::1}” == “:” ]; then mysql –defaults-group-suffix="$p" “${@:2}”; else mysql “$@”; fi; }’
’export -f my’
> /etc/profile.d/mysql-bash-function.sh
Purge old MySQL installation
Great source: cloudify-recipes/services/mysql
service mysql stop
apt-get -y -q purge mysql-client* mysql-server* mysql-common*
rm -rf /var/lib/mysql* # datadir
rm -rf /etc/mysql # config leftovers
Install MySQL 5.6
Use MySQL official deb package in Ubuntu Installing MySQL from Generic Binaries
apt-get install gdebi
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.10-debian6.0-x86_64.deb
gdebi mysql-5.6*.deb
By default, it’s now installed in /opt/mysql/ (was: /usr/local), and the datadir is now /opt/mysql/server-5.6/data (was: /var/lib/mysql)
# Create a mysql user and group
groupadd mysql
useradd -r -g mysql mysql
# /bin directory contains client programs and the server
# Add it to your PATH environment as permanent
#
printf "%s\n%s" \
"export MYSQL_HOME=/opt/mysql/server-5.6" \
"export PATH=\$PATH:\$MYSQL_HOME/bin" \
> /etc/profile.d/mysql-server.sh
printf "%s\n%s" \
"# Mandatory for ruby-mysql on MySQL 5.6.10" \
"export MYSQL_UNIX_PORT=/tmp/mysql.sock" \
> /etc/profile.d/mysql-ruby-fix.sh
# Use that path immediatly
source /etc/profile.d/mysql-server.sh
# Lets go to the installation directory
cd /opt/mysql/server-5.6
# Ensure the ownership to mysql by executing
#
chown -R mysql:mysql .
# Initialize the database.
# Creates the data directory with mysql as the owner
#
scripts/mysql_install_db --user=mysql
# Most of the files can be owned by root. But the data directory
# must be owned by mysql.
chown -R root .
chown -R mysql data
# Set default options
#
# cp support-files/my-default.cnf /etc/my.cnf
#
# Create some files in /etc/mysql/my.cnf
# Start mysqld as boot time service.
# Default service name: mysql.server
#
cp support-files/mysql.server /etc/init.d/mysql
# Start the server
service mysql start
# Set the root password (there is none right now).
mysqladmin -u root password 'devbox'
mysqladmin -u root password 'devbox' -h $HOSTNAME --password='devbox'
# Tighten security:
# Removing Anonymous Accounts
mysql_secure_installation
Script will allow the removing the test databases and anonymous user created by default.
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;"
Very important
Must update apparmor or disable it: File: /etc/apparmor.d/usr.sbin.mysqld After editing: sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld
[How to I move MySQL data files onto different partition? - Ask Ubuntu](http://askubuntu.com/questions/224/how-to-i-move-mysql-data-files-onto-different-partition)
[How to disable AppArmor](http://dijks.wordpress.com/2012/07/06/how-to-disable-apparmor-on-ubuntu-12-04-precise/)
New stuff: MySQL 5.6 Chef recipe unbounce/mysql56-playground · GitHub
List all innodb tables
SELECT table_schema AS db, table_name AS tablename FROM information_schema.tables WHERE engine = ‘innodb’ AND table_schema != ‘mysql’;
MySQL 5.6 Hot Backup
Backup schema.
# Just copy .frm files.
Backup data.
FLUSH TABLE x FOR EXPORT;
# There are .cfg (not really mandatory), .frm and .idb files... Copy them now
# Must be done with system command, because tables are unlocked on exit
SYSTEM cp -a source destination
UNLOCK TABLES;
# There is no .cfg file anymore
Restore schema
# Shutdown server
# Copy .frm (schema file)
# Names of the tables MUST be the same. But, they can be in different databases.
# Start server.
Restore data
* Discard tablespace for the table
* Copy back .idb (data file), .cfg (temporary file) files.
Testing
# Create but remove existing data
# CREATE TABLE y (dummy INT);
# ALTER TABLE y DISCARD TABLESPACE;
# Import data
ALTER TABLE y IMPORT TABLESPACE;
# Now copy the data
# You can use SYSTEM command
# SYSTEM cp -a source destination
cp ../x.* .
rename -vf 's/x\./y\./' x.*
chown mysql:mysql y.*
chmod 660 y.*
# Show warnings if any
SHOW WARNINGS;
# Check structure
SHOW CREATE TABLE y;
# And show data
SELECT * FROM y LIMIT 5;
Sources: » Smarter InnoDB transportable tablespace management operations Todd’s MySQL Blog
Methods to find out internal Table ID Recovering an InnoDB table from only an .ibd file. « Chris on MySQL Chris Calender view on MySQL, SkySQL, MariaDB, Performance Tuning, Data Recovery, MySQL Proxy, Calendar, Calender Connecting orphaned .ibd files - MySQL Performance Blog
MySQL Backup scripts
Bash scripts
mysql_backup/bin/mysql_backup at master · fnichol/mysql_backup · GitHub backup_mysql/backup_mysql.sh at master · Azelphur/backup_mysql · GitHub mysql-backup/mysql-backup at master · aaronknister/mysql-backup · GitHub
Interesting - in parallel: mysql-backup-restore/mysql_backup at master · djteej/mysql-backup-restore · GitHub
Interesting queries
List tables with specific engine type (MyISAM / InnoDB):
SELECT table_name FROM information_schema.tables WHERE table_schema='dbname' AND engine='InnoDB';
List tables with partitons (more than one):
SELECT DISTINCT t.table_schema, t.table_name FROM information_schema.partitions AS p
INNER JOIN information_schema.tables AS t ON p.table_schema = t.table_schema AND p.table_name = t.table_name
LIMIT 10;
WHERE p.partition_number <> 1;
x
Timings
On VM devbox (all fastest disks):
-
Copying packed database dump od aroung 15GB took 11 minutes. From shared Windows dir to the Linux external storage.
-
Devbox: Restoring datingvip_a6, datingvip_w1 = 83 minutes
-
Hetznr: Restoring datingvip_a6, datingvip_w1 = 44 minutes
datingvip_a6 size: 680M datingvip_w1 size: 6.6G
Restoring everything: 1164 min = 19.5 hours 15GB packed, unpacked 166 GB.
Working database takes a lot to start, and consumes 2.2G of RAM to work.
-
Hetznr: MySQL shut down. Packed all the databases with .7z Archive takes up 20G. Took 687 minutes = 12 hrs
Show defaults from config files
my_print_defaults mysqld (or mysql, or …)