All the following commands assumes we saved MySQL root password into MROOTPASS variable:
export MROOTPASS=<mysql root password>
The most straight forward method to backup all the MySQL databases and all the website files (PHP scripts, images, etc..) stored in the /home directory is the following:
mysqldump --all-databases -u root -p$MROOTPASS | gzip > all-databases-$(date '+%Y-%m-%d_%H-%M-%S').sql.gz
tar -cvzf home.tar.gz /home
If we backup some individual database (probably not as root) and change its user while restoring it, it might make a sense to remove DEFINER from the output script:
sed -e 's/*]*\*/\*/'
The following commands restore all the websites from the archives:
gunzip -c all-databases-2017-05-23_15-31-00.sql.gz | mysql -u root -p$MROOTPASS
sudo tar -xvzf home.tar.gz
After migration from MySQL version 14.14 Distrib 5.5.54 to 14.14 Distrib 5.7.18 (I do not know what is the difference between them) I got the following error: “ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted” while trying to drop some user, and fixed it by running:
mysql_upgrade -u root -p$MROOTPASS
service mysql restart
Fist install MySQL client libraries and check their location:
apt-get install libmysqlclient-dev
find / -name '*libmysqlclient*'
Then install SQLite:
sudo apt-get install sqlite3 libsqlite3-dev
Extract GDAL sources and configure supported modules (MySQL and SQLite are not compiled by default, so we need to specify them explicitly):
./configure --with-sqlite3 --with-mysql
this will output “MySQL support: yes” and “SQLite support: yes” along with other information.
Probably, the easiest way to encrypt MySQL database in Ubuntu is by using ecryptfs-utils. Install ecryptfs-utils:
apt-get install ecryptfs-utils
Mount /usr/local/encrypted directory and create mdf directory for MySQL data files (you will be prompted for passphrase and other options):
mount -t ecryptfs /usr/local/encrypted /usr/local/encrypted
chug.sh mysql mdf
chmod og-rwx mdf
In 2008 when I worked with some Oracle databases under Solaris and AIX, I spent some time to figure out how to make the database backup and restore and decided to use cold backup as the most straightforward method. As far as I remember, to backup the database I shutted down Oracle and then simply archive the database files using “zip” command. To restore the database I used the following shell script that extracts archived files and adjust some Oracle settings:
In general, getting EF work with MySQL is a fairly simple task, that could be accomplished by downloading and installing ADO.NET driver for MySQL. But what concerns to me, it taken me about four hours to clarify some MySQL-specific details that affect generation of associations in Model Designer. Also after doing an experimentation with the code I realized that ADO.NET driver for MySQL, as well as other third party ADO.NET drivers, do not support “MARS” and, as far as I see, this significant restriction makes EF unusable with MySQL in large real-life projects. Please read below if you interested in more information on this questions.