MySQL

how to convert a table with MySQL

USE dbname
ALTER TABLE wp_comments ENGINE=InnoDB;

how to convert all tables in a database

for i in `mysql -BN -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = 'dbname'"`; do echo $i; mysql -e "ALTER TABLE $i ENGINE=InnoDB;"; done

Checking max connections and if it was hit

mysql -e "show variables like '%max_connections%';"

mysql -e "show global status like '%max_u%';"

Explaining mysqladmin command output

   | 4268 | TESTDB_USER | localhost | TESTDB_DB1 | Query | 361 | Sending data | SELECT COUNT(*) AS numrows FROM (tblarchivedomain) WHERE title LIKE '%google.com%' OR `doma |

If you're not familiar with the output from the mysqladmin command, this starts by displaying the MySQL process ID, followed by the MySQL user (TESTDB_USER), the server the connection is from (localhost), the database the connection is to (TESTDB_DB1), the command being run (Query), the time that is has currently been running for in seconds (361), the state of that command (sending data) and the information of that command which in this case is part of the query data.

Setting my.cnf

## Good starting points are 64M, then double this value as RAM doubles. 1GB RAM = 64M, 2GB = 128M, 4GB = 256M. Should not be set higher than 4GB( can be removed from the deployed version of the file, just added this comment to explain how this should scale)
key_buffer                              = 256M
## Good starting points are 64M, then double this value as RAM doubles. 1GB RAM = 64M, 2GB = 128M, 4GB = 256M, etc, etc (can be removed from the deployed version of the file, just added this comment to explain how this should scale)
innodb_buffer_pool_size                = 512M
## Misc settings that can be changed if you have a reason to change them, otherwise these should be good starting points ##
max_allowed_packet                      = 16M
table_open_cache                        = 1024
innodb_additional_mem_pool_size        = 8M
## If ACID compliant transactions are not required, this can be changed to either 0 or 2 for better performance at the risk of transaction loss.
#innodb_flush_log_at_trx_commit          = 1
innodb_log_buffer_size                  = 8M
innodb_file_per_table
## Changing this setting requires you to stop MySQL, move the current logs out of the way, and then starting MySQL ##
innodb_log_file_size                    = 128M
## Diagnostics ##
#slow_query_log                          = 1
#slow_query_log_file                    = /var/lib/mysql/slowquery.log
#long_query_time                        = 1
## Maintenance and Recovery Settings ##
default-storage-engine=MyISAM

MySQL ops and highest historic number of connections since MySQL was last started

mysqlops()
{
echo -e "$Green\n=== MySQL Configuration ===$NoColor\n"
echo "MySQL Version: `mysql -V | awk '{print $5}' | cut -d',' -f1`"
mysqladmin extended-status | grep Max_used_connections | awk '{print $2,$4}'
sed -n '/\[mysqld\]/,/\[/p' $MYSQL|grep -v "\["|grep "[a-zA-Z0-9]"


echo "I/O Wait: `echo ${IOSTAT[1]}`" 

http://wiki.mikejung.biz/index.php?title=MySQL

recreating a DB

Destroy and re-create the new database

mysql -e "DROP DATABASE theDATA_new;" mysql -e "CREATE DATABASE theDATA_new;"

Dump and import data from the old to the new

mysqldump theDATA_work > /backup/theDATA_work.sql mysql theDATA_new < /backup/theDATA_work.lwbak.sql

Get the checksums from both of the databases:

for i in $( mysql -e 'use theDATA_work; show tables\G' |grep -v ^'*'|cut -d: -f2 ); do

  echo "$i"
  mysql -e "CHECKSUM TABLE theDATA_work.$i;" | tail -n1 | awk '{print $2}'
  echo

done > /root/db_checksums_1.txt

for i in $( mysql -e 'use theDATA_new; show tables\G' |grep -v ^'*'|cut -d: -f2 ); do

  echo "$i"
  mysql -e "CHECKSUM TABLE theDATA_new.$i;" | tail -n1 | awk '{print $2}'
  echo

done > /root/db_checksums_2.txt

And then diff the files:

diff /root/db_checksums_1.txt /root/db_checksums_2.txt

Dumping

To dump all mysql DB's into their own file

for i in `mysql -e "show databases;"`; do mysqldump $i > /home/mysqlbackups/$i.sql; done

or in one file

mysqldump --all-databases > all_databases.sql

or in the backup dir

mkdir -p /backup/mysqldumps  cd /backup/mysqldumps  for i in $(mysql -e "show databases;" | grep -v Database); do echo dumping $i; `mysqldump $i > $i.sql`; done

Playing with tables and other fun stuffs

lists the databases

simply mysql then

show databases;

to load a database

be in the directory of the sql file make sure it is not compressed gunzip if needed

mysql DBNAME < DBFILE

Fragmented Tables

wget -O /scripts/fragmented.sh http://layer3.liquidweb.com/scripts/jhayhoe/fragmented.sh
chmod +x /scripts/fragmented.sh
/scripts/fragmented.sh

Optimization Scripts

While the default configs here are a good starting point. These scripts will help in finding any issues with the users current MySQL config.

Note: I like to run these like : '/scripts/tuning-primer.sh > /root/tuning-primer.txt[n]' This saves the output, so you don't feel compelled to add it as a note to a ticket or admin comments. You can also use it to compare the results after 48 hours. This is a great way to document these changes.

You can run this from my server:

exec 3<&1 && bash <&3 <(curl -sq https://jparks.work/tuning-primer.sh)

or it is offered externally:

wget -O /scripts/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh
chmod 700 /scripts/tuning-primer.sh
/scripts/tuning-primer.sh 

Another version here:

wget -O /scripts/mysqltuner.pl https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod 700 /scripts/mysqltuner.pl
/scripts/mysqltuner.pl

Optimizing a MySQL database server is beyond this tutorial but for the majority of our customers these will do the job. Edit /etc/my.cnf and enter the recommended settings.

Here's a nice little optimize script that reorganizes all the indexes for all tables on the machine. Good for when mysql indexes are fragmented. Fragmentation like this can cause load on the server and this script is pretty helpful a majority of the time.

This script can crash forums. Use judiciously, and remember to schedule it to run during off-peak hours.}}

for i in $(mysql -e "show databases;" | sed 's/Database//') ; do for each in $(mysql -e "use $i; show tables;" \
| sed 's/Tables.*//' ;) ; do mysql -e "use $i ; optimize table $each" ; done ; done

Not every table is fragmented and needs optimization. Similar to how mysqltuner.pl checks for fragmented tables, the following command recognizes all databases and checks for only fragmented tables.

for i in `mysql -e "show databases;"|sed 's/Database//'`; do for each in `mysql -e "show table status where Data_free > 0;" $i|tail -n +2|grep -v InnoDB|awk '{print $1}'`; do mysqlcheck -o $i $each;    done ; done

Older MySQL versions cannot run the command "show table status where Data_free > 0;". This one-liner is modified for older MySQL versions.

for i in `mysql -e "show databases;"|sed 's/Database//'`; do for each in `mysql -e "show table status;" $i|tail -n +2|awk '{print $1,$10}'|grep -v " 0"|grep -v InnoDB|awk '{print $1}'`; do mysqlcheck -o $i $each; done ; done

Alternatively you can do this (and rebuild join order hints) from mysqlcheck:

mysqlcheck -Asa && mysqlcheck -Aso

A few custom configurations

query_cache_size=64M     # 32MB for every 1GB of RAM unless it is VPS and then use no more than 8MB for each 512M of RAM (4M is probably best for most sites)
key_buffer=64M           # 64MB for every 1GB of RAM
sort_buffer_size=1M      #  1MB for every 1GB of RAM
read_buffer_size=1M      #  1MB for every 1GB of RAM
read_rnd_buffer_size=1M  #  1MB for every 1GB of RAM

MySQL 5.5.xx will not start if you add either of the following two options into my.cnf
safe-show-database 
skip-locking

The following configurations are only meant as a starting point. These often need tweaking (sometimes large amounts of it) depending on the customers needs. Use common sense and the tuning primer to aid you in optimizing mysql.

MAKE A BACKUP OF THE OLD CONFIGURATION FILE BEFORE CHANGING ANYTHING!!! Do NOT simply copy and paste these in as you could seriously break folks innoDB if it is present. Please use these as a guideline only and not an end all to optimization}}

For Legacy VPS, Vservers, and very old hardware

For Servers with MySQL 5.1 and higher, the following changes will need to be made:

Remove 'safe-show-database'
Change 'skip-locking' -> 'skip-external-locking'

[mysqld]
max_connections = 50
safe-show-database
skip-locking
key_buffer = 16M
open_files_limit = 8190
table_cache = 3184
thread_cache = 2
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 8M
max_heap_table_size = 32M
tmp_table_size = 32M
wait_timeout = 30
interactive_timeout = 150

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M

For Dedicated and Storm Servers

1-2G RAM

For Servers with MySQL 5.1 and higher, the following changes will need to be made:

Remove 'safe-show-database'
Change 'skip-locking' -> 'skip-external-locking'

[mysqld]
max_connections = 50
safe-show-database
skip-locking
key_buffer = 128M
open_files_limit = 8190
table_cache = 3184
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
query_cache_size = 32M
max_heap_table_size = 48M
tmp_table_size = 48M
thread_cache_size = 8
wait_timeout = 30
interactive_timeout = 300

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 96M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 96M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

3-8G RAM

For Servers with MySQL 5.1 and higher, the following changes will need to be made:

Remove 'safe-show-database'
Change 'skip-locking' -> 'skip-external-locking'

[mysqld]
max_connections = 64
safe-show-database
skip-locking
key_buffer = 192M
open_files_limit = 8190
table_cache = 3184
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
query_cache_size = 96M
max_heap_table_size = 64M
tmp_table_size = 64M
thread_cache_size = 16
wait_timeout = 45
interactive_timeout = 600

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

More than 8G RAM

For Servers with MySQL 5.1 and higher, the following changes will need to be made:

Remove 'safe-show-database'
Change 'skip-locking' -> 'skip-external-locking'

[mysqld]
max_connections = 128
safe-show-database
skip-locking
key_buffer = 384M
open_files_limit = 8190
table_cache = 3184
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
query_cache_size = 128M
max_heap_table_size = 64M
tmp_table_size = 64M
thread_cache_size = 32
wait_timeout = 120
connect_timeout = 10
interactive_timeout = 1200

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

MySql

to find mysql version type mysql then:

SHOW VARIABLES LIKE "%version%";

A row and a record are the same thing in MySQL SQL statements end with a semi-colon

MYSQL daemon /usr/sbin/mysqld

mySQL data directory is in /var/lib/mySQL

mySQL error log /var/lib/mysql/hostname.err

MySqlAdmin status shows whats going on on the server

mysqldump - exports database info

mysqldump db_name > db_name.sql  (exports to a file)
mysqldump db_name table_name > db_name-Table_name.sql (exports specified table to a file)
mysqldump db_name1, db_name2 > db_names.sql (exports 2 databases to a file)

to reload from a dumped database switch the arrow

mysqldump dbname < db_name.sql

myisam tables are simple and good for begginners and they use alot less memory

do not delete the log file for inodb it can be used to rebuild the DB

check out the scripts for tuning in the LW wiki do not run the script immediatly after starting mySQL wait at least 48 hrs so that peak usage can hit so it can give an accurate reading.

Mysqlcheck -aor --all-databases (defrags and repairs databases) requires alot of space in var oor tmp and could crash the server so make sure their is room

BEFORE DOING A MYSQL REPAIR DO A DU

myisamchk cannot be ran while the server is running!

MySQL databases are found in /var/lib/mysql/cpanel_accountname sometimes and may have 3 different file extensions for each database. save your work by preforming a mysql dump this requires including the cpanel name sometimes (wordpress)

cust would like help importing a DB for his wordpress site would like wp_calendar imported into wp_my_calendar vspac is the name of the cpanel account and folder

backup both files

mysqldump vspac_main wp_calendar > wp_calendar.sql.bak 
mysqldump vspac_main wp_my_calendar > wp_my_calendar.sql.bak

mysqldump vspac_main wp_my_calendar < wp_calendar.sql.bak

service mysql restart

Correct way to upgrade mysql in cpanel box

Firstly Check the versions currently installed (after the upgrade easy apache wont report the php version so we do this first.)

 php -v
 mysqladmin version

Back up the databases before anything else.

mkdir /home/sqlbackup
for db in `mysql -s -B -e "show databases"`;do mysqldump $db > /home/sqlbackup/$db.sql.`date +%Y%m%d%I`;done

Change mysql-version to the version you want.

vim /var/cpanel/cpanel.config

change mysql-version to what you want (IE...5.1, 5.5))

To do 5.1 to 5.5
    sed -i -e 's#mysql-version=5.1#mysql-version=5.5#g' /var/cpanel/cpanel.config
To do 5.5 to 5.6
    sed -i -e 's#mysql-version=5.5#mysql-version=5.6#g' /var/cpanel/cpanel.config

READ THE DAMN THING, make sure there arent errors on the screen

/usr/local/cpanel/whostmgr/bin/whostmgr2 --updatetweaksettings

  READ THE DAMN THING, make sure there arent errors on the screen

/scripts/mysqlup --force
mysqladmin version

   Make sure the EA options all match current versions.

/scripts/easyapache
  
Grats if you got here with no errors

Innodb Corruption Repair

https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/

INNODB DATABASE REPAIR

set recovery to 4. dumping DBs.

root@server:[/backup/mysql-restore/dumps]: for DB in $(mysql -Bse "show databases;"|grep -E -v "performance_schema|information_schema"); do echo "dumping ${DB}"; mysqldump --single-transaction $DB > $DB.sql; done

shutdown mysql and re-syncd the datadir

root@server:[~]: rsync -atv /var/lib/mysql/ /backup/mysql-restore/mysql/ --exclude=centos6.template.liquidweb.com.err

moved and recreated datadir

root@server:[~]: mv /var/lib/mysql{,.old}
root@server:[~]: mkdir /var/lib/mysql
root@server:[~]: chown mysql. /var/lib/mysql
root@server:[~]: chmod 751 /var/lib/mysql
root@server:[~]: mysql_install_db --datadir=/var/lib/mysql --user=mysql

disabled innodb recovery and started mysql

root@server:[~]: vim /etc/my.cnf
root@server:[~]: /etc/init.d/mysql start
Starting MariaDB.180911 20:33:45 mysqld_safe Logging to '/var/lib/mysql/centos6.template.liquidweb.com.err'.

180911 20:33:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

i also had to comment out several things from my.cnf in order to get a stable mysql running after fresh install

#local-infile=0
#max_connections=80
#thread_cache_size=128
#key_buffer=64M
#max_connect_errors=20
#thread_concurrency=4
#concurrent_insert=2
#local-infile=0
#default-storage-engine=MyISAM

restoring mysql db

root@server:[~]: rsync -atv --exclude=*.ibd /backup/mysql-restore/mysql/mysql/ /var/lib/mysql/mysql/

root@server:[~]: /etc/init.d/mysql start

Starting MariaDB.180911 20:44:39 mysqld_safe Logging to '/var/lib/mysql/centos6.template.liquidweb.com.err'. 180911 20:44:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

restoring dbs

root@server:[/backup/mysql-restore/dumps]: mv mysql.sql ../
root@server:[/backup/mysql-restore/dumps]: for DB in $(\ls|cut -d. -f1); do echo "creating ${DB}"; mysqladmin create $DB; mysql $DB < $DB.sql; done

those completed, and everything looks good at this point

root@server:[/backup/mysql-restore/dumps]: mysqlcheck -Aas

uncommented innodb_file_per_table

Overview

So, with MySQL, permissions on databases/tables is much like permissions on files in Unix/LInux land. You need permission to add new rows to a table before you can add those rows, and for even finer control, you need permission at a particular IP.

To add to the fun, the way users and grants are set up got changed in MySQL 5.0.x. This means that some things aren't there, like the mysql.users table.

In this wiki, we're first going to cover MySQL grants for MySQL 5, and then we'll go over the differences in MySQL 4.x

If you're on a cPanel box

If you're on a cPanel box, do ALL of this through cPanel. On cPanel boxes, don't create databases or users directly. Doing this directly through MySQL on a cPanel box breaks cPanel database mapping, and the databases/users won't be backed up. Don't do it.

If you DO decide to not heed this warning, you can correct the mapping by running this command:

/usr/local/cpanel/bin/setupdbmap

Giving someone permissions to do stuff

Assuming you're on MySQL 5.0+

To create a new user

<source lang="mysql"> CREATE USER 'jack'@'localhost' IDENTIFIED BY 'mypass'; </source>

OR

<source lang="mysql"> CREATE USER 'jack'@'%' IDENTIFIED BY 'mypass'; </source>

OR

<source lang="mysql"> CREATE USER 'jack'@'69.16.222.127' IDENTIFIED BY 'mypass'; </source>

A note on unique users

Let's look at creating a user, at a specific IP, with a specific password. It's important to keep in mind, you can have a username at localhost, you can have the same username at 69.16.222.%, you can have the same username at 69.16.222.127, and the same username at %. (% is the wildcard by the way). However, if you have all four of these, that's 4 users. The username is paired to the IP, and you have four seperate password fields in this case.

To demonstrate this, let's look at a query:

<source lang="mysql"> mysql> SELECT user, host, password FROM mysql.user; +---------------+------------------------------------+-------------------------------------------+ | user | host | password | +---------------+------------------------------------+-------------------------------------------+ | root | localhost | *B145E398D3DCFFFE82B2DC502E28AAC590EC07FF | | root | 101.dc2-vps-template.liquidweb.com | *B145E398D3DCFFFE82B2DC502E28AAC590EC07FF | | root | 127.0.0.1 | *B145E398D3DCFFFE82B2DC502E28AAC590EC07FF | | leechprotect | localhost | *E8057E90AB8D4E51239A06C87FF1007ECEEB5414 | | eximstats | localhost | *4C78EFC745475E0748EC4EBA1EB9901D83C4C880 | | cphulkd | localhost | *11FB5E15B6989F4F33181104BB303B7E4C226DE3 | | cpldap | localhost | *61697BCBA52977DFF38254BF1723AF2240039A65 | | roundcube | localhost | *945077E9B53337701826CC615E128D3BD3102495 | | horde | localhost | *15FB21CA888B3182E17B01102EC6CBE7B45BCE81 | | modsec | localhost | *65E12BD8940E523B6C35780D0B255C1E300475B0 | | jhayhurs | localhost | *B2EE6F1F6C7712CF4D2B38EC8A4BE9759E2EF63F | | jhayhurs | 192.168.1.% | *B2EE6F1F6C7712CF4D2B38EC8A4BE9759E2EF63F | | imscared | localhost | 06e0a7e85fb72a63 | | mail | localhost | 29fc7d4049d4cdfb | | jhayhurs_rage | localhost | *E0A58E1FFFD420C25B63DE31B705C995304B12C1 | | jhayhurs_rage | 192.168.1.% | *E0A58E1FFFD420C25B63DE31B705C995304B12C1 | +---------------+------------------------------------+-------------------------------------------+ 16 rows in set (0.00 sec)

mysql> </source>

Giving a user permissions to a database

Remember in MySQL 5.x and later, everything will revolve around users. Users are an object in the database which is granted permission to administer the RDBMS, or administer individual databases, or perform particular functions on databases. This means that the authentication realm for that user is consistent across the entire RDBMS, and when you update their password, you only have to do this in one place.

To give a user all privileges to all tables in a database (lwtest), simply use the following statement:

<source lang="mysql"> mysql> GRANT ALL

   ->     ON lwtest.*
   ->     TO 'somebody'@'localhost';

</source>

What if you only want some privileges?

<source lang="mysql"> mysql> GRANT SELECT,INSERT,UPDATE,DELETE

   ->     ON lwtest.*
   ->     TO 'somebody'@'localhost';

</source>

What if you only want one table?

<source lang="mysql"> mysql> GRANT SELECT,INSERT,UPDATE,DELETE

   ->     ON lwtest.sometable
   ->     TO 'somebody'@'localhost';

</source>

It's similar adding permissions to other stuff.

Showing permissions on a database

Remember, now, you need permissions to do whatever you want to do on the table. You can use:

<source lang="mysql"> mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B145E398D3DCFFFE82B2DC502E28AAC590EC07FF' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) </source>

Or you can specify a user or location:

<source lang="mysql"> mysql> show grants for root@localhost; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B145E398D3DCFFFE82B2DC502E28AAC590EC07FF' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) </source>

If you want to see the grants for a specific user for all locations, you can leave off the @host portion, and it will do a wildcard search. However, if you want to look for all users, you are going to have to go about this differently however. You have to manually pull the users from the internal MySQL tables.

Changing a user's password

Pretty simple. The passwords are hashed internally in MySQL, so you can't retrieve them, but you can't change them. So you could either do:

<source lang="mysql"> SET PASSWORD FOR 'Zombo'@'localhost' = PASSWORD('newpass'); </source>

Or you can do this by changing the permissions tables directly:

<source lang="mysql"> UPDATE mysql.user SET Password=PASSWORD('newpass')

 WHERE User='Zombo' AND Host='localhost';

FLUSH PRIVILEGES; </source>

If you do the second, you have to make sure that you flush privileges however. Forget that, and it won't work.

MySQL 4

Granting Permissions

So, MySQL 4 doesn't have the same users concept. Unfortunately, this means that the users table doesn't work quite the same. Instead, grants work differently, and you have to add them manually.

<source lang="mysql"> GRANT ALL ON mydb.* TO 'someuser'@'somehost' IDENTIFIED BY PASSWORD 'mysecretpass'; FLUSH PRIVILEGES; </source>

Lots of things change on this. However, lots of people are moving away from MySQL 4, so this is quickly becoming a moot point.

Changing Passwords

You can change this too:

mysql> SET PASSWORD FOR 'username'@'localhost' = PASSWORD('$newpass');

Exporting and Importing

You can drop a copy of all grants for a cpanel server to a flat file with the following single command:

 mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql $@ |   sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' | egrep -v '(root|horde|cphulkd|eximstats|modsec|roundcube)' > mysqlprivs.txt

You can change the "egrep" portion of the command to alter what is dumped. So, if you change "egrep -v '(root....)'" to "egrep cpanuser", then you will only get grants for that one user.

Import this dump into mysql on a different server with:

mysql < mysqlprivs.txt

Alright, so you know how to run some MySQL queries. Enough to be dangerous. Let's learn some more.

Also, it's important to keep in mind when configuring MySQL, better to set things up to do something small very well than do a lot of things badly. Keep this in mind when setting things like max connections, timeouts, and similar things. A small mess doing nothing is always better than a big mess doing nothing - much easier to sort out.

MySQL Variables

First, it's important to see all the variables that we're going to look at. So, let's! Don't get overwhelmed!

<source lang='mysql'> mysql> show variables; +-----------------------------------------+---------------------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | / | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | keep_files_on_create | OFF | | engine_condition_pushdown | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | YES | | have_bdb | NO | | have_blackhole_engine | YES | | have_compress | YES | | have_community_features | NO | | have_profiling | NO | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_example_engine | YES | | have_federated_engine | YES | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_merge_engine | YES | | have_ndbcluster | NO | | have_openssl | NO | | have_ssl | NO | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | hostname | host.jhayhurst.com | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_adaptive_hash_index | ON | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294963200 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 150 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 2146435072 | | myisam_mmap_size | 4294967295 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | net_buffer_length | 8192 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 1024 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | pid_file | /var/lib/mysql/host.jhayhurst.com.pid | | plugin_dir | | | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 8388608 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 262144 | | read_only | OFF | | read_rnd_buffer_size | 524288 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_load_tmpdir | /tmp/ | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 524288 | | sql_big_selects | ON | | sql_mode | | | sql_notes | ON | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | EST | | table_cache | 64 | | table_lock_wait_timeout | 50 | | table_type | MyISAM | | thread_cache_size | 0 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | updatable_views_with_limit | YES | | version | 5.0.92-community | | version_comment | MySQL Community Edition (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | | wait_timeout | 28800 | +-----------------------------------------+---------------------------------------+ 233 rows in set (0.00 sec) </source>

That's right. That's all of them. aren't you excited? Now, let's go over some....

auto_increment_*

Alright, so you can set a column to auto_increment on a table. What this means is that when you add a new row, if/when you don't specify a value for that column, it'll fill it in with the next one. auto_increment_increment sets what that goes up by each time (default is 1) and auto_increment_offset sets what that starts numbering with (if not set in the table structure) default is 1.

concurrent_insert

1 is default, and queues inserts up as you would expect. Read more here:

http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html

datadir

This value specifies where the MySQL datadir is. The MySQL datadir in general contains the MySQL database internal information. You want to avoid changing ANYTHING in this directory however, or even creating new files. Everything in here is used/mounted/put up by MySQL, if you put the wrong thing in here, it can cause problems, crashes, data loss, and worse.

delayed_insert*

When you insert/update/delete a row in a table, you can actually tell MySQL to "wait until you're not so busy" and then make the change. Then, if things in MySQL are a bit busier (like let's say everyone decides to SELECT things at the same time) not everybody has to wait right away.

delayed_insert_limit sets the number of rows to insert in a batch before checking to see if more queries are waiting.

delayed_insert_timeout sets how many seconds to wait for more inserts before doing another batch

delayed_queue_size sets the amount of space to reserve for this queue

hostname

This is the hostname of the server, and can't really be set in MySQL (afaik).

innodb_file_per_table

This is the magical setting. By deafult, MySQL puts all InnoDB data in one set of files - ibdata and ib_logfile0/ib_logfile1. Set this, and you now have an .idb file for each MySQL table - if one goes bad, you just have to remove that one. Very helpful.

This CANNOT be changed under normal circumstances. To change this, there has to be NO InnoDB tables. You can either dump all InnoDB tables out, then drop all InnoDB tables, and switch this, then reimport, or you can convert all of these tables to MyISAM, switch this, then switch them back.

interactive_timeout

This is the magical MySQL setting that you want to change. Sometimes people have a lot of MySQL queries that are stacking up. By setting this lowere, you can prevent them from stacking up. A smaller mess that's not doing anything is always better than a bigger mess that isn't doing anything.

join_buffer_size

When MySQL does a JOIN as a part of a query, it takes the two tables and finds out where they line up, and how. This takes some work. This setting lets you set the size of a buffer that saves the result of this work, and reuses it when it can.

key_buffer_size

Similar to above, but this one caches the table keys.

low_priority_updates

This is related to delayed inserts above. This makes updates low priority, often very useful.

lower_case_table_names

This one's more useful on older Windows systems, where the letters 'A' and 'a' are the same. It only lets you create tables with lower case characters, if you put in a query with any upper case letters in it, it'll get translated to lower case characters before being executed. This is a bit of a pain when you have a table with a upper case character in the table name, you can't touch that table.

max_allowed_packet

This one sets the maximum size of a MySQL query that come into MySQL, or the max length of the query.

max_connections

How many concurrent connections can you have? Remember, better to set this low, get things stable, and keep this low. Caching queries outside of MySQL often helps an application, particularly if the application's heavy on requesting the same information.

pid_file

This is the location of the MySQL PID file.

port

Port that MySQL is running on.

query_cache_size

MySQL can cache complete result sets for SELECT statements via this directive.

"The MySQL query cache holds the exact bits that a completed query returned to the client. When a query cache hit occurs, the server can simply return the stored results immediately, skipping the parsing, optimization, and execution steps.

The query cache is designed to be completely transparent to the application. The application does not need to know whether MySQL returned data from the cache or actually executed the query. As servers have gotten larger and more powerful, the query cache has unfortunately proven not to be a very scalable part of MySQL. It is effectively a single point of contention for the whole server, and it can cause severe stalls on multicore servers.

The query cache can still be a net gain. However, the extra overhead can add up, especially in combination with contention caused by queries trying to lock the cache to perform operations on it. For InnoDB users, another problem is that transactions limit the query cache’s usefulness. When a statement inside a transaction modifies a table, the server invalidates any cached queries that refer to the table, even though InnoDB’s multiversioning might hide the transaction’s changes from other statements. The table is also globally uncacheable until the transaction commits, so no further queries against that table - whether inside or outside the transaction—can be cached until the transaction commits. Long-running transactions can, therefore, increase the number of query cache misses."

--High Performance MySQL 3rd edition - Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz, Derek Balling

query_cache_wlock_invalidate

Considering the risk of contention with locked tables, you may want to experiment with a value of ON or OFF to see if cache invalidation is a factor. Setting this to the non-default value ON can cause higher wait lock times, but can ensure that results are not coming from the query cache if the table has been locked.

sort_buffer_size

You'd be better off optmizing your queries rather than trying to finesse performance out of the sorting operation itself. Setting this to more than 2MB should never happen. Our wiki "opts" have this set to 2MB, but anything more than 256k will give diminishing returns. This is due to memory allocation on behalf of the OS changing from mmap() to malloc() if 256k is exceeded. Tests have shown malloc() to 37x slower in similar use cases.

read_only

Only allows Super user to make changes to databases if this is set.

table_lock_wait_timeout

How long to try to get a table lock before kicking an error.

time_zone

MySQL time zone, if set to SYSTEM time zone is inherited on MySQL startup.

tmpdir

Location for MySQL to put tempoary tables that it needs to write to disk, or it's "saved work" that it's doing. For instance, if this is on a 1G partition (like /tmp) and you repair or optimize a 2G table, it needs to put this table somewhere while it's fixing it. There won't be enough room, and you'll run into problems. This location needs to be readable, writable, and executable by MySQL.

version

Show's MySQL server version. Remember, there's a MySQL server, and mysqlclient. When you type mysql on the command line, you're using the mysql client to connect to the MySQL server.

wait_timeout

This sets how long to wait on a query that's marked itself as waiting before killing the query.

Show Status

You probably don't care about most of these. But there is useful stuff in this one, so give it a try sometime.

<source lang="mysql"> mysql> show status; </source>

There may be some interesting things in here, this is mostly where status related things are stored.

Can be helpful, if you're working on certain things.

mysqltuner

This is covered in so much awesome sauce. SOOOO MUCH AWESOME SAUCE.

But you have to understand what you're doing with each, and understand what it's telling you.

GRANT TRIGGER PERMISSION

Triggers have to be granted by table. Example.

mysql> SHOW GRANTS FOR 'devcred'@'%';

no trigger priv found

mysql> GRANT Trigger ON TABLE devcred_civicrm.abc TO mysql> 'devcred'@'%'; Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'devcred'@'%';

| GRANT TRIGGER ON `devcred_civicrm`.`abc` TO 'devcred'@'%'