Restore MySQL datadir on a new server

You can restore a previously copied\moved MySQL datadir on a new server: 1) Stop the MySQL service service mysql stopservice mysql stop 2) Rename the actual MySQL datadir to something different: mv /var/lib/mysql /var/lib/mysql_originalmv /var/lib/mysql /var/lib/mysql_original 3) Copy the MySQL datadir to restore in the MySQL datadir path: cp -R /backups/mysql /var/libcp -R /backups/mysql /var/lib […]

Backup MySQL datadir without Mysqldump

The fastest way to backup MySQL datadir is: 1) Stop the MySQL service service mysql stopservice mysql stop 2) Copy /var/lib/mysql to another location, i.e: cp -R /var/lib/mysql /root/mysqldatadircp -R /var/lib/mysql /root/mysqldatadir 3) Start the MySQL service service mysql startservice mysql start 4) Compress the new MySQL datadir with tar: tar czf /root/mysqldatadir.tar.gz /root/mysqldatadirtar czf […]

MySQL Find Duplicate Values Records

Fastest ways to find duplicate records in MySQL. If name is the column you want to find duplicates in: SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1;SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1; Is also possible to support multiple columns, like this: SELECT col1, col2, COUNT(*) c FROM […]

MySQL Remove Duplicates (Dupes)

Remove MySQL duplicates in place, without making a new table: ALTER IGNORE TABLE foobar ADD UNIQUE (email);ALTER IGNORE TABLE foobar ADD UNIQUE (email); *** This will keep the oldest duplicate record and erase the newer ones *** Works if index fits in memory and if MySQL < 5.7.4 *** Works fine with MariaDB 10.1 If […]

View all foreign keys to a table or column with MySQL

View all foreign keys to a table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’; View all foreign keys to a column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_COLUMN_NAME = ‘columnname’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM […]

Xen the specified virtual disk could not be found

If you get this error message after you reboot a VM: This operation cannot be performed because the specified virtual disk could not be foundThis operation cannot be performed because the specified virtual disk could not be found Make sure to “Eject” the ISO from the DVD Drive 1.

Bash Execute Multiple MySQL Commands from Shell

Here is a Bash script to run multiple MySQL commands: #!/bin/bash   mysql -u root -p’YOUR_MYSQL_PASSWORD_HERE’ dbname << eof ALTER TABLE site DROP INDEX name, ADD UNIQUE KEY email (email); ALTER TABLE site DROP INDEX name_index; alter table site drop column users; alter table site drop column downloads; alter table site drop column invoices; alter […]

Reset (flush) Iptables rules Linux

Bash script to correctly reset (flush) all iptables rules: #!/bin/bash   # Simple script to reset all iptables rules # Without creating issues like with "iptables -F"   IPTABLES="$(which iptables)"   # Set default policies (accept) for all three default chains $IPTABLES -P INPUT ACCEPT $IPTABLES -P FORWARD ACCEPT $IPTABLES -P OUTPUT ACCEPT   # […]

Simple Bash Iptables Firewall Script

Sample firewall script with iptables to allow only trusted traffic: #!/bin/bash   # Simple firewall script to allow only trusted inbound connections # Useful for load balanced servers to allow only inbound traffic on port 80 from the load balancer # Using /etc/crontab you can run the firewall script at every reboot: # @reboot root […]

Bash Get the size of a file

With stat you can get the size of a file: stat –printf="%s" /path/to/filestat –printf="%s" /path/to/file In Bash you can store the file size in a variable: FILESIZE=$(stat -c%s "/path/to/file")FILESIZE=$(stat -c%s "/path/to/file") And you can check if a file is of 0 bytes: if [ "$(stat -c%s "/path/to/file")" -eq 0 ]; then echo "The file is […]