IT notes

mysql CRC32

If need to calculate the CRC32 of a string, using mysql: $ mysql -e "SELECT CRC32('')" +--------------------+ | CRC32('') | +--------------------+ | 3619153832 | +--------------------+

mysql pager

To use less command to vie the output of a mysql result you could use: mysql> \P less or mysql> pager less Then run something like: mysql> show engine innodb status\G In some cases you may need to search for something in specific for this grep could be used, for example: mysql> \P grep -A7 "ROW OPERATIONS" PAGER set to 'grep -A7 "ROW OPERATIONS"' Then when running the command it will only return 7 lines after “ROW OPERATIONS”

galera SOS

Check status of cluster: Find process list not sleeping: SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id; Get locked tables: show open tables where in_use > 0; Finding locks: show engine innodb status; At the end: ---TRANSACTION 1135701157, ACTIVE 6768 sec MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx my_user cleaning up kill the transaction thread:

galera alter

To prevent bloking the cluster while doing ALTERS (SCHEMA UPGRADES), do this per node: SET wsrep_OSU_method='RSU'; Then Run the ALTER statement once done reset the Schema Upgrade method back to Total Order Isolation. SET wsrep_OSU_method='TOI';


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:


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


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;