MySQL
From Athenaeum
Clustering
- You may have to perform a full SST in the event other tables are out of sync. The most aggressive way to do this is the following:
- Since there is no /var/lib/mysql/galera.cache, IST cannot be done. SST would be initiated.
service mysql stop rm -f /var/lib/mysql/galera.cache /var/lib/mysql/grastate.dat service mysql start
- Glera Cluster Node States
variable wsrep_local_state This variable shows internal Galera state number. Possible values are: 1 - Joining (requesting/receiving State Transfer) - node is joining the cluster 2 - Donor/Desynced - node is the donor to the node joining the cluster 3 - Joined - node has joined the cluster 4 - Synced - node is synced with the cluster
MySQL Check
- This is a sample xinetd config file.
# default: on # description: mysqlchk service mysqlchk { # this is a config for xinetd, place it in /etc/xinetd.d/ disable = no flags = REUSE socket_type = stream port = 9200 #bind = 10.10.11.151 wait = no user = nobody server = /usr/bin/clustercheck server_args = clustercheck 0k0rN0t 1 /var/log/clustercheck.log 1 /etc/my.cnf log_on_failure += USERID only_from = 10.10.0.0/16 127.0.0.1 per_source = UNLIMITED # Passing arguments to clustercheck # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file> # Recommended: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.local # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra # # recommended to put the IPs that need # to connect exclusively (security purposes) }
Replication
- Set up a slave server (quick way)
- mysqldump --user=root --password --all-databases --master-data | gzip > backup.sql.gz
- zcat backup.sql.gz | mysql --user=root --password db_name
- SHOW SLAVE STATUS \G;
- START SLAVE;
- Note: the --master-data option only includes MASTER_LOG_FILE and MASTER_LOG_POS arguments of the "CHANGE MASTER TO" statement; you'll likely want to edit the dump to include MASTER_HOST, MASTER_USER and MASTER_PASSWORD values when importing to a fresh slave server (or realize that you'll need to re-issue a CHANGE MASTER TO statement after the import).
Tricks
- Get sizes of dbs
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
- Function to view and transfer grants.
- Put the following in your bashrc
mygrants() { mysql -B -N [email protected] -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';' ) AS query FROM mysql.user" | \ mysql [email protected] | \ sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' }
- Then run the following
mygrants --host=prod-db1 --user=admin --password=secret | grep user_name | mysql --host=staging-db1 --user=admin --password=secret
- Script to optimize tables
#!/bin/sh mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do mysql -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do if [ "$datafree" -gt 0 ] ; then fragmentation=$(($datafree * 100 / $datalength)) echo "$database.$name is $fragmentation% fragmented." mysql -NBe "OPTIMIZE TABLE $name;" "$database" fi done done
Random Info
- Creating a user account
create user 'username'@'10.10.0.%' identified by 'password';
grant all privileges on dbname.* to 'username'@'10.10.0.%' with grant option;
- Dump commands
mysqldump --single-transaction --routines --triggers --events --all-databases | pv | gzip -6 -c > {path_to_file}.sql.gz
mysqldump --single-transaction --routines --triggers --events --set-gtid-purged=OFF --databases my_videos_99 | pv | gzip -6 -c > /tmp/all_databases.sql.gz
- Restore
pv /tmp/all_databases.sql | mysql
- GTID Off and Only 10,000 lines
mysqldump --single-transaction --routines --triggers --set-gtid-purged=OFF --opt --where="1 limit 10,000" vdc_release | gzip > prod-vdc_release.sql.gz
- Log Rolling
- Percona Backups
- Purge Binlogs
- purge binary logs before date(now() - interval 3 day) + interval 0 second;
- this will erase all binary logs before midnight 3 days ago.
- Update Definers
- UPDATE mysql.proc p SET definer = '[email protected]%' WHERE definer<>'[email protected]%' AND db='dev_release_s01';