IT notes

Import large dump

To import a large dump (~300GB) you could do: Split your file first: csplit -s -ftable dump.sql "/-- Table structure for table/" {999} You could also use the pattern {*} but you need the gcsplit: pkg install coreutils BSD csplit don’t support {*} Then try: gcsplit -s -ftable dump.sql "/-- Table structure for table/" {*} Create small INSERT chunks: gsplit -a 3 -d -n l/200 table05 x_ l/N split into N files without splitting lines/records

Delete in chunks

To delete a big/huge table in chunks, you could create this store procedure, but the performance at the end depends of how good/normalized is your database (indexes): A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Such procedures are stored in the database data dictionary. Connect to the database and select the database: mysql> \u my_database Database changed To list stored procedures

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; Doing a heavy DDL (ALTER): SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND info LIKE 'alter%' AND time >= 2 ORDER BY time DESC, id LIMIT 1; DDL stands for data definition language. It is a set of SQL statements that work on the database as a whole, rather than an individual table or row.

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: +----------------+----------------------+----------------------+----------------------+ | Storage Engine | Data Size | Index Size | Table Size | +----------------+----------------------+----------------------+----------------------+ | InnoDB | 10.

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;