Category Archives: Development

Software or web development

Copying or moving all of MySQL databases

This is just one of the ways you can move all of the MySQL databases from one server to another.  This was tested on Ubuntu 16.04.01 LTS (xenial) distro.

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new SQL dump.

$ mysql -u root -p < ~/files/dbs.sql

IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you’re doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally.  On one of the servers I had it for 1024M just for this operation and brought it back low afterwards.

Recommended file permissions for WordPress

Private WordPress installations seem to be the drum that hackers like to hit on these days.  It seems that many themes are also vulnerable as well. I’m not saying this is the answer to mitigate hacking attempts, but merely as a starting point.  Anyway, as a starting point for fortifying a WP installation here are recommended file permissions settings for a WordPress installation on a Linux box.  These commands will set 750 for all folders, 640 for files, and 600 for wp-config.php file. Sitting with a root/sudo access on a parent folder from a WP installation folder (assuming wpfolder here).


# find wpfolder -type d -exec chmod 750 {} +
# find wpfolder -type f -exec chmod 640 {} +
# chmod 600 wpfolder/wp-config.php

 

You may also want to make sure that only wp-content folder is owned by www-data (or whatever your webserver may be using).

Adding sudoers

A file can be added for groups of users or specific users to /etc/sudoers.d/ directory. This line would make someone a sudoer with no password requirement.

jsmith ALL=(ALL) NOPASSWD:ALL

If you want the user to type a password.

jsmith ALL=(ALL:ALL) ALL

Updating sudoers file safely

  • Last checked on Ubuntu 16.04.01 LTS (xenial)

The command visudo checks the validity of the sudoers file before making the actual update to the file, and this is the recommended way of editing the file because one can potentially lose sudo privileges unintentionally.

$ sudo visudo

Instead editing /etc/sudoers file I usually create a file at /etc/sudoers.d/localusers so I edit that instead.

$ sudo visudo -f /etc/sudoers.d/localusers

Changing the default editor used for visudo

I’m a VIM user, but many of the distros default to nano for newcomers to Linux systems. You can use the following command to change the default editor that is loaded for visudo and for many other apps.

$ sudo update-alternatives --config editor

 

Moving all of the databases from one server to another

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new dumped SQL file.

$ mysql -u root -p < ~/files/dbs.sql

https://wiki.michaelhan.net/MySQL