rowty.net code snippets Public source code sharing repository https://rowty.net Change MySQL master password on slave - rowty.net code snippets CHANGE MASTER TO MASTER_PASSWORD='newpass'; https://rowty.net/view/123/1/Change-MySQL-master-password-on-slave https://rowty.net/view/123/1/Change-MySQL-master-password-on-slave Tue, 28 Aug 2018 13:28:06 GMT Change MySQL / MariaDB user password - rowty.net code snippets # For version 5.7.5 and earlier: SET PASSWORD FOR 'rob'@'localhost' = PASSWORD('MyNewPass'); # For versions after version 5.7.5 ALTER USER 'rob'@'localhost' IDENTIFIED BY 'MyNewPass'; https://rowty.net/view/121/1/Change-MySQL---MariaDB-user-password https://rowty.net/view/121/1/Change-MySQL---MariaDB-user-password Mon, 6 Aug 2018 11:36:25 GMT Check MySQL users for empty passwords - rowty.net code snippets # Run the following SELECT User,host FROM mysql.user WHERE (plugin IN('mysql_native_password', 'mysql_old_password') AND (LENGTH(Password) = 0 OR Password IS NULL)) OR (plugin='sha256_password' AND LENGTH(authentication_string) = 0); https://rowty.net/view/120/1/Check-MySQL-users-for-empty-passwords https://rowty.net/view/120/1/Check-MySQL-users-for-empty-passwords Thu, 5 Jul 2018 11:19:36 GMT Check for MySQL users with passwords set to their usernames - rowty.net code snippets # Run this to check SELECT User,Password,Host FROM mysql.user WHERE password=CONCAT('*', UPPER(SHA1(UNHEX(SHA1(user))))); https://rowty.net/view/119/1/Check-for-MySQL-users-with-passwords-set-to-their-usernames https://rowty.net/view/119/1/Check-for-MySQL-users-with-passwords-set-to-their-usernames Thu, 5 Jul 2018 11:11:42 GMT 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