Raspberry Pi to Banana Pi migration

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&gt; GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'n4aSHUP04s1J32X5';</code>
mysql&gt; FLUSH PRIVILEGES;

Now your mysql-server should start again without errors.

Leave a Reply

  Subscribe  
Notify of