MySQL - mwicat/personal GitHub Wiki

Install

sudo apt-get install gnupg2
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release setup ps57
sudo apt-get install percona-server-server-5.7 percona-server-client-5.7

Lock database

FLUSH TABLES WITH READ LOCK
UNLOCK TABLES;

Wipe

sudo apt-get remove --purge percona-server-server-5.6 percona-server-client-5.6 percona-server-common-5.6
sudo apt-get install percona-server-server-5.6 percona-server-client-5.6 percona-server-common-5.6

Add access

GRANT ALL PRIVILEGES ON database.* TO 'newuser'@'localhost';

Show access / list users

select Host, User from mysql.user;

sudo apt install percona-toolkit

pt-show-grants

Duplicate row

select group_concat(column_name order by ordinal_position) from information_schema.columns where table_schema = 'database_name' and table_name = 'table_name';
INSERT INTO table (col1, col2, col3) SELECT col1, col2, col3  FROM table WHERE id = 1

Mirror empty databases

mysqldump --all-databases --no-data

Export databases

mysqldump -u root -p --databases db1 db2 > dbdump.sql

Export database tables

mysqldump -u root -p database_name tbl1 tbl2 > dbdump.sql

Import database tables

mysql -u root -p database_name < dbdump.sql

Export rows to CSV

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Get insert clause from select statement

mysqldump -t mydb mytable --no-create-db --no-create-info --complete-insert --single-transaction --where="ID = myid"

Show databases size in MB

SELECT table_schema AS db, SUM(data_length + index_length) / 1024 / 1024 AS size_in_mb FROM information_schema.TABLES GROUP BY table_schema order by size_in_mb desc;

Show tables size in MB

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) size_in_mb FROM information_schema.TABLES WHERE table_schema = "yourdbname" ORDER BY size_in_mb DESC;

Show index size in MB

SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' ORDER BY size_in_mb DESC;

Replication

Dump and import data

ssh masterhost mysqldump --no-data --all-databases | mysql
ssh masterhost mysqldump --master-data --databases db1 db2 | mysql

Fix master info

CHANGE MASTER TO MASTER_HOST='yourhost', MASTER_USER='replication', MASTER_PASSWORD='yourpass';

Show replication info

cat /var/lib/mysql/master.info

Fix replication

https://mariadb.com/kb/en/set-global-sql_slave_skip_counter/

SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Show slave status

SHOW SLAVE STATUS\G

Show connected slaves

SHOW SLAVE HOSTS;

Log queries to file

/etc/mysql/my.cnf

[mysqld]
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Show query statistics since last restart

show global status like "Com_select";
show global status like "Com_update";
show global status like "Com_insert";
show global status like "Com_delete";

Reset root password

service mysql stop
mysqld_safe --skip-grant-tables &
mysql

UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
FLUSH PRIVILEGES;
exit;

mysqladmin -u root -p shutdown
service mysql start

Smoke test

create table dummy (`id` int not null auto_increment primary key, `name` varchar(10) ); 
insert into dummy (name) values ('name1');