CodeVoyager

MySQL Tricks & Tools

Edit Page

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

iask/myxtrabackupex · GitHub

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 …)

date 29. Mar 2015 | modified 10. Jun 2024
filename: MySQL » Tricks & Tools
Article Content