mysql¶

Dump a MySQL-Database to another Database¶

mysqldump force -uUSER -pPASS PRODUCTION_DB | mysql -uUSER -pPASS COPY_DB

Convert mysql database from latin1 to utf8¶

mysqldump add-drop-table -uroot -p "DB_name"  | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p "DB_name"

show mysql process ids¶

mysql -s -e "show processlist" |awk '{print $1}'

raw MySQL output to use in pipes¶

mysql DATABASE -N -s -r -e 'SQL COMMAND'

Get column names in MySQL¶

mysql -u <user> password=<password> -e "SHOW COLUMNS FROM <table>" <database> | awk '{print $1}' | tr "\n" "," | sed 's/,$//g'

command line to drop all table from a databse¶

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

Count the number of queries to a MySQL server¶

mysql -uUser -pPassword -N -s -r -e 'SHOW PROCESSLIST' | grep -cv "SHOW PROCESSLIST"

import database¶

mysql>use DBNAME;  mysql>source FILENAME

Create a mysql database from the command line¶

mysqladmin -u username -p create dbname

See where MySQL is looking for its config files¶

mysql -? | grep ".cnf"

Copy a MySQL Database to a new Server via SSH with one command¶

mysqldump add-drop-table extended-insert force log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS NEW_DB_NAME"

Display the number of connections to a MySQL Database¶

mysql -u root -p -BNe "select host,count(host) from processlist group by host;" information_schema

Backup all MySQL Databases to individual files¶

mysql -e 'show databases' | sed -n '2,$p' | xargs -I DB 'mysqldump DB > DB.sql'

backup local MySQL database into a folder and removes older then 5 days backups¶

mysqldump -uUSERNAME -pPASSWORD database | gzip > /path/to/db/files/db-backup-`date +%Y-%m-%d`.sql.gz ;find /path/to/db/files/* -mtime +5 -exec rm {} \;

Create MySQL-Dump, copy db to other Server and upload the db.¶

mysqldump -uUserName -pPassword tudb | ssh root@rootsvr.com "mysql -uUserName -pPassword -h mysql.rootsvr.com YourDBName"

dump a single table of a database to file¶

mysqldump -u UNAME -p DBNAME TABLENAME> FILENAME

Show all usernames and passwords for Plesk email addresses¶

mysql -uadmin -p` cat /etc/psa/.psa.shadow` -Dpsa -e"select mail_name,name,password from mail left join domains on mail.dom_id = domains.id inner join accounts where mail.account_id = accounts.id;"

Pulls email password out of Plesk database for given email address.¶

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "use psa; select accounts.password FROM accounts JOIN mail ON accounts.id=mail.account_id WHERE mail.mail_name='webmaster';"

Mysql extended status¶

mysqladmin -u root -p extended-status

Mysql uptime¶

mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'

Drop all tables from a database, without deleting it¶

mysqldump -u $USER password=$PASSWORD  add-drop-table no-data "$DATABASE" | grep ^DROP | mysql -u $USER password=$PASSWORD "$DATABASE"

Create a backup copy of a MySQL database on the same host¶

mysqldump OLD_DB | cat <(echo "CREATE DATABASE NEW_DB; USE NEW_DB;") - | mysql

Analyze, check, auto-repair and optimize Mysql Database¶

mysqlcheck -a auto-repair -c -o -uroot -p [DB]

Convert all MySQL tables and fields to UTF8¶

mysql database=dbname -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql database=dbname &

Execute MySQL query send results from stdout to CSV¶

mysql -umysqlusername -pmysqlpass databsename -B -e "select * from \`tabalename\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv

command line to optimize all table from a mysql database¶

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u uname dbname

Get a list of the top 10 heaviest tables in your mysql database - useful for performance diagnostics¶

mysql database=information_schema -u <user> -p -e "SELECT TABLE_NAME, TABLE_SCHEMA, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 mb FROM TABLES GROUP BY TABLE_NAME ORDER BY mb DESC LIMIT 10"

mysql DB size¶

mysql -u root -pPasswort -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'

how to export a table in .csv file¶

mysql -u[username] -p[password] [nome_database] -B -e "SELECT * FROM [table] INTO OUTFILE '/tmp/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Get table column names from an MySQL-database in comma-seperated form¶

mysql -u<user> -p<password> -s -e 'DESCRIBE <table>' <database>  | tail -n +1 | awk '{ printf($1",")}' |  head -c -1

Dump and bz2compress a mysql db¶

mysqldump -u user -h host -ppwd -B dbname | bzip2 -zc9 > dbname.sql.bz2

mysql pager="less -niSFX"

Calculating number of Connection to MySQL¶

mysql -NBe 'show global status like "Threads_connected";' | cut -f2

Dump mySQL db from Remote Database to Local Database¶

mysqldump host=[remote host] user=[remote user] password=[remote password] -C db_name | mysql host=localhost user=[local user] password=[local password] db_name

Show database sql schema from Remote or Local database¶

mysqldump -u<dbusername>  -p<dbpassword> <databasename>  no-data tables

mysql bin log events per minute¶

mysqlbinlog <logfiles> | grep exec | grep end_log_pos | cut -d' ' -f2- | cut -d: -f-2 | uniq -c

get a mysqldump with a timestamp in the filename and gzip it all in one go¶

mysqldump [options] |gzip ->mysqldump-$(date +%Y-%m-%d-%H.%M.%S).gz

Export a subset of a database¶

mysqldump where="true LIMIT X" databasename > output.sql

How to extract 5000 records from each table in MySQL¶

mysqldump opt where="true LIMIT 5000" dbinproduzione > miodbditest.sql

Remote mysql dump all databases with ssh¶

mysqldump -u user -p all-databases | ssh user@host dd of=/opt/all-databases.dump

dump the whole database¶

mysqldump -u UNAME -p DBNAME > FILENAME

dump the whole database¶

mysqldump lock-tables opt DBNAME -u UNAME password=PASS | gzip > OUTFILE

Backup all MySQL Databases to individual files¶

mysql -e 'show databases' -s skip-column-names | egrep -v "^(test|mysql|performance_schema|information_schema)$" | parallel gnu "mysqldump routines {} > {}_daily.sql"

Export mysql database to another database without having to save the output first¶

mysqldump -u<username> -p<password> -h<source database host> databasename table1 table2 table_n | mysql -u<user> -p<password> -h<destination database host> databasename

mysqlbinlog headers sorted by event time¶

mysqlbinlog <logfiles> | grep exec | grep end_log_pos | grep -v exec_time=0 | sed 's/^\(.*exec_time=\([0-9]\+\).*\)/\2 - \1 /' | sort -n

do replication and sql backup¶

mysqldump -pyourpass single-transaction  master-data=2 -q  flush-logs databases db_for_doslave |tee /home/db_bak.sql |ssh mysqladmin@slave.db.com "mysql"

Back up only databases matching PATTERN¶

mysqldump -eBv `echo "show databases like 'MYSQL_PATTERN'"|mysql -N`> OUTPUTFILE

mysql: Convert MyISAM tables to InnoDB via mysqldump¶

mysqldump | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/'

Repair MySQL db¶

mysqlcheck -r -u Admin -p all-databases

command line to optimize all table from a mysql database¶

mysqlcheck -op -u<user> <db>

MySQL: Slice out a specific table from a specific database (assumes existence of the USE statement) from output of mysqldump¶

mysqldump | sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^ Table structure for table \`departments\`/,/^ Table structure for table/p"

Dump MySql to File¶

mysqldump opt -uUSERNAME -pPASSWORD -h mysql.host.com database > ~/filename.sql

get mysql full processlist from commadline¶

mysqladmin -u user -ppassword verbose  processlist sleep 1

Run query on remote database and output results as csv¶

mysql -u[user] -p[password] -h [hostname] -D [database] -ss -e "select * from mysql_tbl " | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > dump.csv

Exporting all MySQL user privileges¶

mysql -u{user} -p{password} -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql -u {user} -p{password} | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Exclude dumping of specific tables with same prefix from a single database¶

mysql -uuser -ppass -e 'use information_schema; SELECT table_name FROM tables where table_schema="DB-NAME" and table_name NOT LIKE "PREFIX";' | grep -v table_name | xargs mysqldump DB-NAME -uuser -ppass > dump.sql

mysql defaults-file=/etc/mysql/debian.cnf

Monitor MySQL threads per user¶

mysql -BNe "SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;" information_schema

Monitor MySQL threads per user¶

mysql -BNe "SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;" information_schema

Kill all threads from a MySQL user¶

mysql -BNe "SELECT id FROM processlist WHERE user = 'redmine';" information_schema | while read id; do mysqladmin kill $id; done

backup with mysqldump a really big mysql database to a remote machine over ssh¶

mysqldump -q skip-opt force log-error=dbname_error.log -uroot -pmysqlpassword dbname | ssh -C user@sshserver 'cd /path/to/backup/dir; cat > dbname.sql'

Backup a whole database to a file¶

mysqldump -p MYDB > MYDB.sql

Pulls FTP password out of Plesk database.¶

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "use psa; select accounts.password from accounts INNER JOIN sys_users ON   accounts.id=sys_users.account_id WHERE sys_users.login='xxxx';"

Calculating number of Connection to MySQL¶

mysql -u root -p -e"show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq -c

Calculating number of Connection to MySQL¶

mysql -u root -p -N -e"show processlist\G;" | egrep "Host\:" | awk -F: '{ print $2 }' | sort | uniq -c

Convert mysql database from latin1 to utf8¶

mysqldump add-drop-table -uroot -p "DB_name"  | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p "DB_name"

raw MySQL output to use in pipes¶

mysql DATABASE -N -s -r -e 'SQL COMMAND'

Copy a MySQL Database to a new Server via SSH with one command¶

mysqldump add-drop-table extended-insert force log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS NEW_DB_NAME"

Display the number of connections to a MySQL Database¶

mysql -u root -p -BNe "select host,count(host) from processlist group by host;" information_schema

Backup all MySQL Databases to individual files¶

mysql -e 'show databases' | sed -n '2,$p' | xargs -I DB 'mysqldump DB > DB.sql'

backup local MySQL database into a folder and removes older then 5 days backups¶

mysqldump -uUSERNAME -pPASSWORD database | gzip > /path/to/db/files/db-backup-`date +%Y-%m-%d`.sql.gz ;find /path/to/db/files/* -mtime +5 -exec rm {} \;

Create MySQL-Dump, copy db to other Server and upload the db.¶

mysqldump -uUserName -pPassword tudb | ssh root@rootsvr.com "mysql -uUserName -pPassword -h mysql.rootsvr.com YourDBName"

dump a single table of a database to file¶

mysqldump -u UNAME -p DBNAME TABLENAME> FILENAME

Show all usernames and passwords for Plesk email addresses¶

mysql -uadmin -p` cat /etc/psa/.psa.shadow` -Dpsa -e"select mail_name,name,password from mail left join domains on mail.dom_id = domains.id inner join accounts where mail.account_id = accounts.id;"

Pulls email password out of Plesk database for given email address.¶

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "use psa; select accounts.password FROM accounts JOIN mail ON accounts.id=mail.account_id WHERE mail.mail_name='webmaster';"

Mysql extended status¶

mysqladmin -u root -p extended-status

Mysql uptime¶

mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'

Drop all tables from a database, without deleting it¶

mysqldump -u $USER password=$PASSWORD  add-drop-table no-data "$DATABASE" | grep ^DROP | mysql -u $USER password=$PASSWORD "$DATABASE"

Create a backup copy of a MySQL database on the same host¶

mysqldump OLD_DB | cat <(echo "CREATE DATABASE NEW_DB; USE NEW_DB;") - | mysql

Analyze, check, auto-repair and optimize Mysql Database¶

mysqlcheck -a auto-repair -c -o -uroot -p [DB]

Convert all MySQL tables and fields to UTF8¶

mysql database=dbname -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql database=dbname &

Execute MySQL query send results from stdout to CSV¶

mysql -umysqlusername -pmysqlpass databsename -B -e "select * from \`tabalename\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv

command line to optimize all table from a mysql database¶

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u uname dbname

Get a list of the top 10 heaviest tables in your mysql database - useful for performance diagnostics¶

mysql database=information_schema -u <user> -p -e "SELECT TABLE_NAME, TABLE_SCHEMA, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 mb FROM TABLES GROUP BY TABLE_NAME ORDER BY mb DESC LIMIT 10"

mysql DB size¶

mysql -u root -pPasswort -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'

how to export a table in .csv file¶

mysql -u[username] -p[password] [nome_database] -B -e "SELECT * FROM [table] INTO OUTFILE '/tmp/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Get table column names from an MySQL-database in comma-seperated form¶

mysql -u<user> -p<password> -s -e 'DESCRIBE <table>' <database>  | tail -n +1 | awk '{ printf($1",")}' |  head -c -1

Dump and bz2compress a mysql db¶

mysqldump -u user -h host -ppwd -B dbname | bzip2 -zc9 > dbname.sql.bz2

mysql pager="less -niSFX"

Calculating number of Connection to MySQL¶

mysql -NBe 'show global status like "Threads_connected";' | cut -f2

Dump mySQL db from Remote Database to Local Database¶

mysqldump host=[remote host] user=[remote user] password=[remote password] -C db_name | mysql host=localhost user=[local user] password=[local password] db_name

Show database sql schema from Remote or Local database¶

mysqldump -u<dbusername>  -p<dbpassword> <databasename>  no-data tables

mysql bin log events per minute¶

mysqlbinlog <logfiles> | grep exec | grep end_log_pos | cut -d' ' -f2- | cut -d: -f-2 | uniq -c

get a mysqldump with a timestamp in the filename and gzip it all in one go¶

mysqldump [options] |gzip ->mysqldump-$(date +%Y-%m-%d-%H.%M.%S).gz

Export a subset of a database¶

mysqldump where="true LIMIT X" databasename > output.sql

How to extract 5000 records from each table in MySQL¶

mysqldump opt where="true LIMIT 5000" dbinproduzione > miodbditest.sql

Remote mysql dump all databases with ssh¶

mysqldump -u user -p all-databases | ssh user@host dd of=/opt/all-databases.dump

dump the whole database¶

mysqldump -u UNAME -p DBNAME > FILENAME

dump the whole database¶

mysqldump lock-tables opt DBNAME -u UNAME password=PASS | gzip > OUTFILE

Backup all MySQL Databases to individual files¶

mysql -e 'show databases' -s skip-column-names | egrep -v "^(test|mysql|performance_schema|information_schema)$" | parallel gnu "mysqldump routines {} > {}_daily.sql"

Export mysql database to another database without having to save the output first¶

mysqldump -u<username> -p<password> -h<source database host> databasename table1 table2 table_n | mysql -u<user> -p<password> -h<destination database host> databasename

mysqlbinlog headers sorted by event time¶

mysqlbinlog <logfiles> | grep exec | grep end_log_pos | grep -v exec_time=0 | sed 's/^\(.*exec_time=\([0-9]\+\).*\)/\2 - \1 /' | sort -n

do replication and sql backup¶

mysqldump -pyourpass single-transaction  master-data=2 -q  flush-logs databases db_for_doslave |tee /home/db_bak.sql |ssh mysqladmin@slave.db.com "mysql"

Back up only databases matching PATTERN¶

mysqldump -eBv `echo "show databases like 'MYSQL_PATTERN'"|mysql -N`> OUTPUTFILE

mysql: Convert MyISAM tables to InnoDB via mysqldump¶

mysqldump | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/'

Repair MySQL db¶

mysqlcheck -r -u Admin -p all-databases

command line to optimize all table from a mysql database¶

mysqlcheck -op -u<user> <db>

MySQL: Slice out a specific table from a specific database (assumes existence of the USE statement) from output of mysqldump¶

mysqldump | sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^ Table structure for table \`departments\`/,/^ Table structure for table/p"

Dump MySql to File¶

mysqldump opt -uUSERNAME -pPASSWORD -h mysql.host.com database > ~/filename.sql

get mysql full processlist from commadline¶

mysqladmin -u user -ppassword verbose  processlist sleep 1

Run query on remote database and output results as csv¶

mysql -u[user] -p[password] -h [hostname] -D [database] -ss -e "select * from mysql_tbl " | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > dump.csv

Exporting all MySQL user privileges¶

mysql -u{user} -p{password} -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql -u {user} -p{password} | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Exclude dumping of specific tables with same prefix from a single database¶

mysql -uuser -ppass -e 'use information_schema; SELECT table_name FROM tables where table_schema="DB-NAME" and table_name NOT LIKE "PREFIX";' | grep -v table_name | xargs mysqldump DB-NAME -uuser -ppass > dump.sql

mysql defaults-file=/etc/mysql/debian.cnf

Monitor MySQL threads per user¶

mysql -BNe "SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;" information_schema

backup with mysqldump a really big mysql database to a remote machine over ssh¶

mysqldump -q skip-opt force log-error=dbname_error.log -uroot -pmysqlpassword dbname | ssh -C user@sshserver 'cd /path/to/backup/dir; cat > dbname.sql'

Last update: August 10, 2022
Created: August 10, 2022

mysql¶

Dump a MySQL-Database to another Database¶

Convert mysql database from latin1 to utf8¶

show mysql process ids¶

raw MySQL output to use in pipes¶

Get column names in MySQL¶

command line to drop all table from a databse¶

Count the number of queries to a MySQL server¶

import database¶

Create a mysql database from the command line¶

See where MySQL is looking for its config files¶

Copy a MySQL Database to a new Server via SSH with one command¶

Display the number of connections to a MySQL Database¶

Backup all MySQL Databases to individual files¶

backup local MySQL database into a folder and removes older then 5 days backups¶

Create MySQL-Dump, copy db to other Server and upload the db.¶

dump a single table of a database to file¶

Show all usernames and passwords for Plesk email addresses¶

Pulls email password out of Plesk database for given email address.¶

Mysql extended status¶

Mysql uptime¶

Drop all tables from a database, without deleting it¶

Create a backup copy of a MySQL database on the same host¶

Analyze, check, auto-repair and optimize Mysql Database¶

Convert all MySQL tables and fields to UTF8¶

Execute MySQL query send results from stdout to CSV¶

command line to optimize all table from a mysql database¶

Get a list of the top 10 heaviest tables in your mysql database - useful for performance diagnostics¶

mysql DB size¶

how to export a table in .csv file¶

Get table column names from an MySQL-database in comma-seperated form¶

Dump and bz2compress a mysql db¶

Change MySQL Pager For Nicer Output¶

Calculating number of Connection to MySQL¶

Dump mySQL db from Remote Database to Local Database¶

Show database sql schema from Remote or Local database¶

mysql bin log events per minute¶

get a mysqldump with a timestamp in the filename and gzip it all in one go¶

Export a subset of a database¶

How to extract 5000 records from each table in MySQL¶

Remote mysql dump all databases with ssh¶

dump the whole database¶

dump the whole database¶

Backup all MySQL Databases to individual files¶

Export mysql database to another database without having to save the output first¶

mysqlbinlog headers sorted by event time¶

do replication and sql backup¶

Back up only databases matching PATTERN¶

mysql: Convert MyISAM tables to InnoDB via mysqldump¶

Repair MySQL db¶

command line to optimize all table from a mysql database¶

MySQL: Slice out a specific table from a specific database (assumes existence of the USE statement) from output of mysqldump¶

Dump MySql to File¶

get mysql full processlist from commadline¶

Run query on remote database and output results as csv¶

Exporting all MySQL user privileges¶

Exclude dumping of specific tables with same prefix from a single database¶

mysql login as privileged user on multiple machines with differend mysql root password¶

Monitor MySQL threads per user¶

Monitor MySQL threads per user¶

Kill all threads from a MySQL user¶

backup with mysqldump a really big mysql database to a remote machine over ssh¶

Backup a whole database to a file¶

Pulls FTP password out of Plesk database.¶

Calculating number of Connection to MySQL¶

Calculating number of Connection to MySQL¶

Convert mysql database from latin1 to utf8¶

raw MySQL output to use in pipes¶

Copy a MySQL Database to a new Server via SSH with one command¶

Display the number of connections to a MySQL Database¶

Backup all MySQL Databases to individual files¶

backup local MySQL database into a folder and removes older then 5 days backups¶

Create MySQL-Dump, copy db to other Server and upload the db.¶

dump a single table of a database to file¶

Show all usernames and passwords for Plesk email addresses¶

Pulls email password out of Plesk database for given email address.¶

Mysql extended status¶

Mysql uptime¶

Drop all tables from a database, without deleting it¶

Create a backup copy of a MySQL database on the same host¶