Moving parts of the file-system to an external HDD
In contrast to a Raspberry Pi, the Banana Pi features a SATA connector with power supply port. Moving parts of the file-system to an external hdd potentially increases system-performance and adds a lot of storage-space which is particularly useful for large MySQL databases.
However, before you consider moving an existing MySQL database to an external drive, it is important to make a backup copy of your database first! Furthermore, suspend the mysql-server daemon by issuing the command
sudo service mysql stop
On Debian Wheezy or Jessie, the default location of the MySQL database is the directory /var/lib/mysql. It is possible to copy the whole directory including all databases to a new location on /dev/sda1. Before you can do so, you must add a new line in /etc/fstab to configure a mount point for the drive, i.e.
/dev/sda1 /home ext4 defaults, noatime, nodiratime, data=writeback, discard, commit=600, errors=remount-ro 0 0
Make sure to set permissions and ownership of the mysql directory to
drwx------ 4 mysql mysql 4096 Dec 30 12:12 mysql
Be aware that this is not possible in case your drive is formatted with a vfat or ntfs file system! Next you must change /etc/mysql/my.cnf to point the mysql-server to its new database location. Therefore, edit the ‘datadir’ variable with in the [mysqld] block. If you want remote access to your database (not recommended), you must change the bind address to 0.0.0.0. With:
sudo netstat -ntlup | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 987/mysql
you can figure out to which address the server daemon is listening to. In case that you have migrated your database from a different server, the mysql-server daemon most likely will complain about:
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: '<strong>Access denied for user 'debian-sys-maint'@'localhost'</strong> (using password: YES)'
The debian-sys-maint user is created to start and stop the database and to carry out other maintenance operations. In order to fix this issue, you have to update the password for the debian-sys-maint user. It is stored in /etc/mysql/debian.conf:
# Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint <strong>password = n4aSHUP04s1J32X5</strong> socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] user = debian-sys-maint <strong>password = n4aSHUP04s1J32X5</strong> socket = /var/run/mysqld/mysqld.sock basedir = /usr
To update the password, log into the mysql root account with:
mysql -u root -p <password>
and update the password using:
<code>mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'n4aSHUP04s1J32X5';</code> mysql> FLUSH PRIVILEGES;
Now your mysql-server should start again without errors.