rowty.net code snippets Public source code sharing repository https://rowty.net Install and set up MariaDB on CentOS 7 - rowty.net code snippets # Install MariaDB - drop-in replacement for MySQL yum install mariadb-server # Enable mariadb to start on boot systemctl enable mariadb # Start it up! systemctl start mariadb # Set a root password, remove anon users, disable root login remotely: mysql_secure_installation https://rowty.net/view/82/1/Install-and-set-up-MariaDB-on-CentOS-7 https://rowty.net/view/82/1/Install-and-set-up-MariaDB-on-CentOS-7 Thu, 2 Mar 2017 11:04:12 GMT monitory the mysql processlist - rowty.net code snippets watch -n 1 mysqladmin processlist https://rowty.net/view/58/1/monitory-the-mysql-processlist https://rowty.net/view/58/1/monitory-the-mysql-processlist Thu, 23 Feb 2017 11:40:38 GMT Back up all mysql databases on server into separate individual files - rowty.net code snippets for db in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "$db.sql.gz"; done https://rowty.net/view/49/1/Back-up-all-mysql-databases-on-server-into-separate-individual-files https://rowty.net/view/49/1/Back-up-all-mysql-databases-on-server-into-separate-individual-files Thu, 23 Feb 2017 11:21:07 GMT Import a mysql database from backup - rowty.net code snippets mysql -u user -p database-name < /home/backups/database-name.sql https://rowty.net/view/36/1/Import-a-mysql-database-from-backup https://rowty.net/view/36/1/Import-a-mysql-database-from-backup Thu, 23 Feb 2017 09:23:55 GMT Drop / delete mysql database from command line - rowty.net code snippets drop database database_name; https://rowty.net/view/32/1/Drop---delete-mysql-database-from-command-line https://rowty.net/view/32/1/Drop---delete-mysql-database-from-command-line Thu, 23 Feb 2017 09:18:09 GMT Add logging on linux for mysql - rowty.net code snippets # edit /etc/my.cnf # To enable error log add following [mysqld] log_error=/var/log/mysql/mysql_error.log # To enable general query log add following general_log_file = /var/log/mysql/mysql.log general_log = 1 # Create the log files: touch... https://rowty.net/view/29/1/Add-logging-on-linux-for-mysql https://rowty.net/view/29/1/Add-logging-on-linux-for-mysql Thu, 23 Feb 2017 09:09:18 GMT Back up mysql database command line - rowty.net code snippets mysqldump database_name > database_name.sql https://rowty.net/view/28/1/Back-up-mysql-database-command-line https://rowty.net/view/28/1/Back-up-mysql-database-command-line Thu, 23 Feb 2017 09:03:57 GMT mysqldumpslow - show top 5 - rowty.net code snippets mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log https://rowty.net/view/26/1/mysqldumpslow---show-top-5 https://rowty.net/view/26/1/mysqldumpslow---show-top-5 Wed, 22 Feb 2017 15:56:24 GMT purge mysql binary logs to 1 day ago - rowty.net code snippets PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 1 day); https://rowty.net/view/23/1/purge-mysql-binary-logs-to-1-day-ago https://rowty.net/view/23/1/purge-mysql-binary-logs-to-1-day-ago Wed, 22 Feb 2017 15:44:16 GMT repair crashed tables on all databases - rowty.net code snippets mysqlcheck -u root -p --auto-repair --optimize --all-databases https://rowty.net/view/15/1/repair-crashed-tables-on-all-databases https://rowty.net/view/15/1/repair-crashed-tables-on-all-databases Wed, 22 Feb 2017 15:25:17 GMT start / stop mysql slave - rowty.net code snippets # On the slave server within mysql: STOP SLAVE; FLUSH TABLES; # On slave server at root prompt: /etc/init.d/mysql stop # # do your stuff here.. reboot, etc.. # # On slave, from within mysql: START SLAVE; SHOW SLAVE STATUS G; https://rowty.net/view/5/1/start---stop-mysql-slave https://rowty.net/view/5/1/start---stop-mysql-slave Wed, 22 Feb 2017 14:46:14 GMT