MySQL Tipps und Tricks
From Lolly's Wiki
Oneliner
Show MySQL-traffic fired from a client
# tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
if (defined $q) { print "$q\n"; }
$q=$_;
} else {
$_ =~ s/^[ \t]+//; $q.=" $_";
}
}'
Mysql processes each second
# mysqladmin -i 1 --verbose processlist
All grants
# mysql --skip-column-names --batch --execute 'select concat("`",user,"`@`",host,"`") from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'
Or a little nicer:
#!/bin/bash
#
## Written by Lars Timmann <L@rs.Timmann.de> 2017
#
function usage () {
cat << EOH
Usage: $0 [--all] [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ...
--help: This output
--grant-user|--gu: You can specify this option several times.
The <pattern> can be:
<user> : You will get grants on all hosts for this user.
@<host> : You will get grants for all users on this host.
<user>@<host> : You will get specific grants for user@host.
The pattern may contain % as wildcard.
If the pattern is @% it shows all grants where host is exactly '%'.
--grant-db|--gdb: You can specify this option several times.
The pattern names the database to look for.
The pattern may contain % as wildcard.
--all: Show all grants
...: Optional parameters to the mysql command
EOH
exit
}
show_all_grants=0
declare -a grant_user
for ((param=1;param<=${#};param++))
do
case ${!param} in
--grant-user|--gu)
param=$[ ${param} + 1 ]
grant_user+=( "${!param}" )
# delete 2 parameters from list and set back $param
set -- "${@:1:param-2}" "${@:param+1}"
param=$[ ${param} - 2 ]
;;
--grant-db|--gdb)
param=$[ ${param} + 1 ]
grant_db+=( "${!param}" )
# delete 2 parameters from list and set back $param
set -- "${@:1:param-2}" "${@:param+1}"
param=$[ ${param} - 2 ]
;;
--all)
show_all_grants=1
# delete 1 parameter from list and set back $param
set -- "${@:1:param-1}" "${@:param+1}"
param=$[ ${param} - 1 ]
;;
--help)
usage
;;
*)
;;
esac
done
count=${#grant_user[@]}
for((param=0;param<count;param++))
do
before=${#grant_user[@]}
grant="${grant_user[${param}]}"
user="${grant%@*}"
if [[ "${grant}" == *\@?* ]]
then
host="${grant/*@}"
else
host=''
fi
case ${host} in
'')
select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where user like '${user}'"
;;
'%')
select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host='${host}' ${user:+and user like '${user}'}"
;;
*)
select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host like '${host}' ${user:+and user like '${user}'}"
;;
esac
grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "${select}" | sort ) "${grant_user[@]:param+1}" )
after=${#grant_user[@]}
param=$[ param + after - before ]
count=$[ count + after - before ]
done
# Get user for database in grant_db array
for db in ${grant_db[@]}
do
grant_user+=( $(mysql $* --silent --skip-column-names --execute "
select concat('\'',user,'\'@\'',host,'\'') as user from mysql.db where db like '${db}';
select concat('\'',user,'\'@\'',host,'\'') as user from mysql.columns_priv where db like '${db}';
select concat('\'',user,'\'@\'',host,'\'') as user from mysql.tables_priv where db like '${db}';
" | sort -u ) )
done
# --all
if [ ${show_all_grants} -eq 1 ]
then
printf -- '--\n-- %s\n--\n' "all grants";
grant_user=( $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user" | sort ) )
fi
for user in ${grant_user[@]}
do
printf -- '--\n-- %s\n--\n' "${user}";
show_create_user="$(mysql $* --silent --skip-column-names --execute "select (substring_index(version(), '.',1) >= 5) and (substring_index(substring_index(version(), '.', 2),'.',-1) >=7) as show_create_user;";)"
if [ "${show_create_user}" -eq 1 ]
then
mysql $* --silent --skip-column-names --execute "show create user ${user};" | sed 's/$/;/'
fi
OLD_IFS=${IFS}
IFS=$'\n'
for grant in $(mysql $* --silent --skip-column-names --execute "show grants for ${user}" | sed 's/$/;/')
do
regex='GRANT[ ]+.*[ ]+ON[ ]+(FUNCTION[ ]+|)`([^`]*)`\..*'
if [[ $grant =~ $regex ]]
then
database=${BASH_REMATCH[2]}
if [ ${#grant_db[@]} -gt 0 ]
then
if [[ " ${grant_db[@]} " =~ " ${database} " ]]
then
echo "${grant}"
fi
else
echo "${grant}"
fi
else
echo "${grant}"
fi
done
done
Last update time
- Per table
mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;
- Per database
mysql> SELECT TABLE_SCHEMA AS DB,MAX(UPDATE_TIME) AS LAST_UPDATE FROM INFORMATION_SCHEMA.TABLES GROUP BY DB ORDER BY LAST_UPDATE;
InnoDB space
Per database
mysql> select table_schema as database_name, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema order by total_size_mb;
Per table
mysql> select table_schema as database_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;
Logging
If you use SET GLOBAL it is just for the moment.
Don't forget to add it in your my.cnf to make it permanent!
What can I log?
The interesting variables here are:
- log_queries_not_using_indexes
- log_slave_updates
- log_slow_queries
- general_log
Choose logging destination FILE/TABLE/NONE
This affects general_log and slow_query_log.
- Log to the table mysql.slow_log and mysql.general_log
mysql> SET GLOBAL log_output=TABLE;
- Log to the table mysql.slow_log and mysql.general_log
mysql> SET GLOBAL log_output=TABLE;
- Both: tables and files
mysql> SET GLOBAL log_output = 'TABLE,FILE';
- None, if NONE appears in the log_output destinations there is no logging
mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';
is equal to
mysql> SET GLOBAL log_output = 'NONE';
Enable/disable general logging
mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
Enable/disable logging of slow queries
mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
Slave
Debugging
What did we see from the master
Read the binlog from Master:
# mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --base64-output=auto --database='limit output to this database' -vv mysql-bin.number | less
if you get
ERROR: Failed on connect: SSL connection error: protocol version mismatch
try
# mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --ssl-mode=DISABLED --base64-output=auto --database='limit output to this database' -vv mysql-bin.number | less
For an idea of the binlog file to investigate on the master do this on your slave:
# mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'
Filesystems for MySQL
ext3/ext4
Create Options
# mkfs.ext4 -b 4096 /dev/mapper/vg--data-lv--ext4--mysql_data
Mountoptions
- noatime
- data=writeback (best performance , only metadata is logged)
- data=ordered (ok performance , recording metadata and grouping metadata related to the data changes)
- data=journal (worst performance, but best data protection, ext3 default mode, recording metadata and all data)
Raw devices with InnoDB
Take a look at setting device permissions via udev first.
After that the device is owned by mysql:
# ls -alL /dev/vg-data/lv-rawdisk-innodb01
brw-rw---- 1 mysql mysql 252, 0 Aug 12 15:07 /dev/vg-data/lv-rawdisk-innodb01
Determine the size:
# lvs vg-data
LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert
lv-rawdisk-innodb01 vg-data -wi-a---- 25.00g
# fdisk -l /dev/vg-data/lv-rawdisk-innodb01
Disk /dev/vg-data/lv-rawdisk-innodb01: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders, total 52428800 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
# bc -l
26843545600/(1024*1024*1024)
25.00000000000000000000
Yes... really 25GB!
Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf :
[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw
Start mysql:
# service mysql start
Aaaaaand.. do not forget apparmor! Like I did.. :-D
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /dev/dm-0
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
# tail /var/log/kern.log
...
Aug 12 15:30:09 mysql kernel: [ 5840.118528] audit: type=1400 audit(1439386209.399:33): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/dev/dm-0" pid=11810 comm="mysqld" requested_mask="wr" denied_mask="wr" fsuid=108 ouid=108
...
Add your raw device to the apparmor config in /etc/apparmor.d/local/usr.sbin.mysqld :
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/dev/dm-* rwk,
Reload apparmor:
# service apparmor reload
Another try!
# service mysql start
InnoDB: The first specified data file /dev/vg-data/lv-rawdisk-innodb01 did not exist:
InnoDB: a new database to be created!
150812 15:48:23 InnoDB: Setting file /dev/vg-data/lv-rawdisk-innodb01 size to 25600 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...
Much better!
So shutdown MySQL again!
Change your configuration /etc/mysql/conf.d/innodb.cnf and change newraw to raw! :
[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
NFS
NFSv4
On NetApp CDOT SVM
cdot1nfsv4::> export-policy rule create -policyname default -clientmatch 172.18.128.0/22 -superuser none -rwrule none -rorule sys -allow-dev false -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> export-policy create -policyname mysql_clients
cdot1nfsv4::> export-policy rule create -policyname mysql_clients -clientmatch 172.18.128.0/22 -superuser sys -rwrule sys -rorule sys -allow-dev true -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> nfs server modify -v4.0 enabled -v4-id-domain this.domain.tld
cdot1nfsv4::> set -units GB
cdot1nfsv4::> vol show -volume MYSQLNFS_* -fields volume,policy,size,junction-path
vserver volume size policy junction-path
------------------ --------------------- ---- ------------- ----------------------
cdot1nfsv4 MYSQLNFS_DATA 40GB mysql_clients /MYSQLNFS_DATA
cdot1nfsv4 MYSQLNFS_LOG 1GB mysql_clients /MYSQLNFS_LOG
2 entries were displayed.
Links:
- How to configure NFSv4 in Cluster-Mode
- Clustered Data ONTAP NFS Expert recommended articles
- How to configure NetApp storage systems for Network File System version 4 in AIX and Linux environments
- How to enable or disable NFSv4 on NetApp storage systems
On Linux
Blacklist rpcsec_gss_krb5
To disable loading of the rpcsec_gss_krb5 kernel module which causes problems with performance, do this:
# echo "blacklist rpcsec_gss_krb5" > /etc/modprobe.d/blacklist-rpcsec_gss_krb5.conf
# rmmod rpcsec_gss_krb5
/etc/sysctl.d/99-mysql.conf
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Semaphores & IPC for optimizations in innodb
kernel.shmmax=2147483648
kernel.shmall=2147483648
kernel.msgmni=1024
kernel.msgmax=65536
kernel.sem=250 32000 32 1024
###################################################################
# Swap
vm.swappiness = 0
vm.vfs_cache_pressure = 50
/etc/sysctl.d/99-netapp-nfs.conf
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Optimization for netapp/nfs increased from 64k, @see http://tldp.org/HOWTO/NFS-HOWTO/performance.html#MEMLIMITS
net.core.wmem_default=262144
net.core.rmem_default=262144
net.core.wmem_max=262144
net.core.rmem_max=262144
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_no_metrics_save = 1
# Guidelines from http://media.netapp.com/documents/mysqlperformance-5.pdf
net.ipv4.tcp_sack=0
net.ipv4.tcp_timestamps=0
sunrpc.tcp_slot_table_entries=128
#nfs.v3.enable on
nfs.tcp.enable=on
nfs.tcp.recvwindowsize=65536
nfs.tcp.xfersize=65536
#iscsi.iswt.max_ios_per_session 128
#iscsi.iswt.tcp_window_size 131400
#iscsi.max_connections_per_session 16
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 1024 65023
net.ipv4.tcp_max_syn_backlog = 10240
net.ipv4.tcp_max_tw_buckets = 400000
net.ipv4.tcp_max_orphans = 60000
net.ipv4.tcp_synack_retries = 3
net.core.somaxconn = 10000
kernel.sysrq=0
net.ipv4.neigh.default.gc_thresh1 = 4096
net.ipv4.neigh.default.gc_thresh2 = 8192
net.ipv4.neigh.default.gc_thresh3 = 8192
net.ipv4.neigh.default.base_reachable_time = 86400
net.ipv4.neigh.default.gc_stale_time = 86400
Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf
mysql soft nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql hard nproc 10240
Modify systemd mysql.service to raise the number of files limit
To raise the number of files for the service you have to tell the systemd the new limit.
# systemctl edit mysql.service
and enter:
[Service]
LimitNOFILE=1024000
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file
...
# /etc/systemd/system/mysql.service.d/override.conf
[Service]
LimitNOFILE=1024000
Do not forget to activate and check the limit
# systemctl daemon-reload
# systemctl restart mysql
# awk 'NR==1 || /Max open files/' /proc/$(pgrep mysqld$)/limits
Limit Soft Limit Hard Limit Units
Max open files 1024000 1024000 files
Modify systemd service to wait for NFS
To be sure that the NFS mount is ready when the mysql server starts add After=nfs-client.target to the systemd service in the Unit-section.
# systemctl edit mysql.service
and enter:
[Unit]
Description=MySQL Community Server
After=network.target
After=nfs-client.target
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file
[Unit]
Description=MySQL Community Server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
# /etc/systemd/system/mysql.service.d/override.conf
[Unit]
Description=MySQL Community Server
After=network.target
After=nfs-client.target
[Service]
LimitNOFILE=1024000
Do not forget to activate the changes...
# systemctl daemon-reload
# systemctl restart mysql
... and check they are active:
# systemctl list-dependencies --after mysql.service | grep nfs-client.target
● ├─nfs-client.target
/etc/idmapd.conf
# Domain = localdomain
Domain = this.domain.tld
# systemctl restart nfs-idmapd.service
Alternative switch off kerberos authentication with the mount option sec=sys.
/etc/fstab
cdot-nfsv4-svm:/MYSQLNFS_LOG /MYSQLNFS_LOG nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime
cdot-nfsv4-svm:/MYSQLNFS_DATA /MYSQLNFS_DATA nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
...
datadir = /MYSQLNFS_DATA/data/mysql
...
/etc/mysql/mysql.conf.d/innodb.cnf
[mysqld]
#
# * InnoDB
#
innodb_data_home_dir = /MYSQLNFS_DATA/InnoDB
innodb_data_file_path = ibdata1:200M:autoextend
innodb_log_group_home_dir = /MYSQLNFS_LOG/ib_log
#innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = on
# mysql -e "show variables where variable_name like '%dir' and value like '/MYSQLNFS%'"
+---------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------+
| datadir | /MYSQLNFS_DATA/data/mysql/ |
| innodb_data_home_dir | /MYSQLNFS_DATA/InnoDB |
| innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log |
+---------------------------+------------------------------------+
/etc/mysql/mysql.conf.d/query_cache.cnf
[mysqld]
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0,00 sec)
mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0,00 sec)
apparmor : /etc/apparmor.d/local/usr.sbin.mysqld
# vim:syntax=apparmor
# This should be always there...
owner @{PROC}/@{pid}/status r,
/sys/devices/system/node/ r,
/sys/devices/system/node/** r,
# The mysql datadir, innodb_data_home_dir
/MYSQLNFS_DATA/ r,
/MYSQLNFS_DATA/** rwk,
# The mysql innodb_log_group_home_dir
/MYSQLNFS_LOG/ r,
/MYSQLNFS_LOG/** rwk,
Short stupid performance test
# time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
65536+0 records in
65536+0 records out
1073741824 bytes (1,1 GB, 1,0 GiB) copied, 1,7552 s, 612 MB/s
real 0m1.772s
user 0m0.016s
sys 0m0.672s
Some things seem to work...
Sample InnoDB configuration
/etc/mysql/conf.d/innodb.cnf
[mysqld]
# InnoDB Parameters
# innodb_buffer_pool_size=(0.7*total_mem_size)
innodb_buffer_pool_size=1433M
# bulk_insert_buffer_size
bulk_insert_buffer_size=256M
# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2
# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4
# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT
# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log
Analyze
mysql> select * from <tablename> PROCEDURE ANALYSE();
mysql> SHOW /*!50000 GLOBAL*/ STATUS;
- See [MySQL Performance Tuning]
Find statements which lead into an error
mysql> select CURRENT_SCHEMA,DIGEST_TEXT,MYSQL_ERRNO,MESSAGE_TEXT from performance_schema.events_statements_history where errors!=0\G
*************************** 1. row ***************************
CURRENT_SCHEMA: NULL
DIGEST_TEXT: NULL
MYSQL_ERRNO: 1046
MESSAGE_TEXT: No database selected
1 row in set (0,00 sec)
percona-toolkit
# aptitude install percona-toolkit
# mysql -e "explain select * from mysql.user,mysql.db where user.user=db.user" | pt-visual-explain
JOIN
+- Bookmark lookup
| +- Table
| | table db
| | possible_keys User
| +- Index lookup
| key db->User
| possible_keys User
| key_len 48
| ref mysql.user.User
| rows 3
+- Table scan
rows 68
+- Table
table user
Sysbench
# mysql -u root -e "create database sbtest;"
# sysbench \
--test=oltp \
--oltp-table-size=10000000 \
--db-driver=mysql \
--mysql-table-engine=innodb \
--mysql-db=sbtest \
--mysql-user=root \
--mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
--mysql-socket=/var/run/mysqld/mysqld.sock \
prepare
# sysbench \
--test=oltp \
--oltp-test-mode=complex \
--oltp-table-size=80000000 \
--db-driver=mysql \
--mysql-table-engine=innodb \
--mysql-db=sbtest \
--mysql-user=root \
--mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
--mysql-socket=/var/run/mysqld/mysqld.sock \
--num-threads=4 \
--max-time=900 \
--max-requests=500000 \
run
# mysql -u root_rw -e "drop table sbtest;" sbtest
Recover a damaged root account
Lost grants
Try out:
# service mysql stop
# echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:14:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start
Or:
# service mysql stop
# mysqld_safe --skip-grant-tables &
...
# mysql -e "UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost';"
# mysqladmin -u root shutdown
# service mysql start
Lost password
# service mysql stop
# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:15:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start
Structured configuration
This is the default in Ubuntus /etc/mysql/my.cnf:
...
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
/etc/mysql/conf.d/innodb.cnf:
[mysqld]
# InnoDB Parameters
# innodb_buffer_pool_size=(0.7*total_mem_size)
#innodb_buffer_pool_size=512M
innodb_buffer_pool_size=256M
# bulk_insert_buffer_size
#bulk_insert_buffer_size=256M
bulk_insert_buffer_size=128M
# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2
# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4
# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT
# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log
/etc/mysql/conf.d/myisam.cnf:
[mysqld]
#key_buffer = 512M
key_buffer = 128M
table_cache = 8K
myisam_sort_buffer_size = 64M
tmp_table_size = 64M
# Variable: concurrent_insert
# Value Description
# 0 Disables concurrent inserts
# 1 (Default) Enables concurrent insert for MyISAM tables that do not have holes
# 2 Enables concurrent inserts for all MyISAM tables, even those that have holes.
# For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread.
# Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.
concurrent_insert=2
# Variable: myisam_use_mmap
# https://www.percona.com/blog/2006/05/26/myisam-mmap-feature-51/
#
myisam_use_mmap=1
/etc/mysql/conf.d/mysqld.cnf:
[mysqld]
datadir = /var/lib/mysql/data/data
# because mysql is soooo stupid
#ignore-db-dirs = lost+found # when we will have mysql >= 5.6.3
bind-address = 127.0.0.1
open-files-limit = 4096
max_connections = 512
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 512
table_cache = 8192
thread_concurrency = 4
default-storage-engine = innodb
# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log
# Print warnings to the error log file. If you have any problem with
# MySQL you should enable logging of warnings and examine the error log
# for possible explanations.
log_warnings
# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
log_slow_queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2
# Log more information in the slow query log. Normally it is good to
# have this turned on. This will enable logging of queries that are not
# using indexes in addition to long running queries.
#log_long_format
log_bin = /var/lib/mysql/binlog/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
sync_binlog = 0
performance_schema = ON
/etc/mysql/conf.d/mysqld_safe.cnf:
[mysqld_safe]
/etc/mysql/conf.d/mysqld_safe_syslog.cnf:
[mysqld_safe]
syslog
/etc/mysql/conf.d/query_cache.cnf:
[mysqld]
query_cache_limit = 4M
query_cache_size = 128M
query_cache_min_res_unit = 2K
MySQL Clients
Small one liners for testing purposes.
PHP
PHP PDO
$ php -r '
$pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
PDO::ATTR_PERSISTENT => true
)
);
$stmt=$pdo->prepare("SELECT * FROM mytable");
if($stmt->execute()){
while($row = $stmt->fetch()){
print_r($row);
}
};
$stmt = null;
$pdo=null;
'
