IT notes

Processlist

Run show processlist every second: mysqladmin -uroot -p -i 1 processlist -i 1 stands for interval one second

mysql SHA512 CRYPT

To create a password using the SHA512-CRYPT schema: SELECT ENCRYPT("password", CONCAT('$6$',sha(RAND()))); To create an user: INSERT INTO users SET password=ENCRYPT("password", CONCAT('$6$',sha(RAND()))); To use with postfix or dovecot: INSERT INTO users SET password=CONCAT('{SHA512-CRYPT}', ENCRYPT("password", CONCAT('$6$',sha(RAND()))));

mysql db size

How big is your data: SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE( FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize, SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B, (SELECT 3 pw) A ORDER BY TSize; This will print something like:

mysql backup

mysqldump + xz + openssl First add to .my.cnf the proper username/password to avoid getting a warning: [client] host = localhost user = dbadmin password = secret To take the dump and keep it only for 31 days: #!/bin/sh DAY=$(date +%d) mysqldump --events --routines --triggers --add-drop-database --compress --hex-blob --opt --skip-comments --single-transaction dbname | \ xz -c | \ openssl smime -encrypt -aes256 -binary -out /safe/path/${DAY}.sql.xz.enc -outform DER /path/to/cert.pem mysqldump The options used:

mysql root password

Reset root password on mysql >= 5.7.17 Edit /etc/rc.conf: mysql_args="--skip-grant-tables --skip-networking" Restart mysql: service mysql-server restart Login like root: mysql> UPDATE mysql.user SET authentication_string = PASSWORD('aaaa') WHERE User = 'root' AND Host = 'localhost'; mysql> FLUSH PRIVILEGES; mysql> quit Remove or comment: # mysql_args="--skip-grant-tables --skip-networking" Restart mysql: service mysql-server restart mysqldump: Couldn’t execute ‘SHOW VARIABLES LIKE ‘gtid_mode”: To fix that try this: # mysql_upgrade -u root -p --force

rds slave from external master

Setup AWS MySQL RDS instance as slave of an external master Configure external mysql to become master: server-id=1 binlog-format= mixed log-bin=mysql-bin binlog-do-db=databasename Setup the master replica user: mysql> create user `replica`@`%` identified by 'PASSWORD'; mysql> grant replication slave on *.* to 'replica'@'%'; Use the following command to ensure that nothing can write to the master database during a database dump. Also note the filename and position of the binary log because you will need these values to complete the replication configuration:

innodb_flush_log_at_trx_commit=2

To speed mysql replica add into the [mysqld]: innodb_flush_log_at_trx_commit=2

mysql_tzinfo_to_sql

FreeBSD & MySQL UTC After doing a fresh install, load the UTC zone: $ cd /usr/share/zoneinfo $ mysql_tzinfo_to_sql UTC UTC | mysql -u root mysql Edit the /etc/my.cnf: [mysqld] default-time-zone='UTC' This will set the default timezone on the server to be UTC To get current timezone of the mysql you can do following things: mysql> SELECT @@global.time_zone, @@session.time_zone;