MySQL / MariaDB Database

mysql -u root -p 

mysqlcheck --optimize --all-databases --host=localhost --user=${DB_USER} --password=${DB_PASS}

apt-get install -y mysql-admin

sed -i 's|bind-address|#bind-address|g' /etc/mysql/my.cnf 
/etc/init.d/mysql restart

GRANT ALL PRIVILEGES ON magento.* TO  dbo_magento@'%' IDENTIFIED BY 'your_pass' 
 
# dont LOCK TABLE on export / import
mysqldump  --single-transaction --quick --skip-trigger ...

mysqldump --no-data ${DB_NAME} ${TABLE_NAME} > ${DB_NAME}.${TABLE_NAME}.$(hostname).$(date -I).sql

DB_USER=foo
DB_PASS=bar
DB_NAME=db1
mysqldump -u ${DB_USER} -p${DB_PASS} --add-drop-table --no-data ${DB_NAME} | grep ^DROP | mysql -u ${DB_USER} -p${DB_PASS} ${DB_NAME}
 
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET utf8 COLLATE utf8_general_ci;

ssh -C -L 3306:db.example.com:3306 foo@gw.example.com -N
echo "show databases;" | mysql --host=127.0.0.1 --port=3306 --user='user' --password='pass'

# listen for localhost connections only
bind-address = 127.0.0.1
# listen for connections on all interfaces
bind-address = 0.0.0.0
# listen for connections only on specific IP
bind-address = 192.168.1.2

telnet 192.168.1.2 3306

apt-get install socat
mkdir -p /var/run/mysqld
socat UNIX-LISTEN:/var/run/mysqld/mysqld.sock,fork,reuseaddr,unlink-early,user=myuser,group=mygroup,mode=777 TCP:db.example.com:3306 &
# test
echo "select @@hostname;" | mysql -u db_user -p

FLUSH TABLES tbl_list WITH READ LOCK;

mysql -u user -pPASS1234  database-name -e 'SQL Query'

mysql --skip-column-names -s -e "SELECT id FROM table1 WHERE name=hsot");

SELECT * FROM instances WHERE display_name = 'foo-dev' \G

# Add column / alter table
ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];
 
# Remove column
ALTER TABLE table_name
DROP COLUMN column_name;
 
# modify column
ALTER TABLE room MODIFY property_id bigint(20) not null;
 
# add primary key
ALTER TABLE USER ADD PRIMARY KEY (C_USRKEY);

SHOW ENGINE INNODB STATUS\G
SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST;
SELECT user, count(*) FROM INFORMATION_SCHEMA.PROCESSLIST group by user;

mysql -e "show slave status\G"
mysql -e "show master status\G"
mysql -e "slave start"
 
# (re)sync
SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';
rm -rf /var/lib/mysql/*
service mysql start