Chapter 6 ■ Building low-Cost MysQl data nodesmysql> SHOW VARIABLES LIKE '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /var/lib/mysql/ |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| lc_messages_dir | /usr/share/mysql/ |
| plugin_dir | /usr/lib/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
11 rows in set (0.00 sec)
mysql>
In the example, I issued the SHOW DATABASES command to see the list of databases and the SHOW VARIABLES
command to show all variables containing the name dir. Notice the datadir output from the last command:
this is the location of your data.
In the next section, you tell MySQL to use the external drive instead for storing your databases and data.
Moving the Data Directory to the External Drive
Recall that you want to use MySQL to store your sensor data. As such, the sensor data may grow in volume
and over time may consume a lot of space. Rather than risk filling up your boot image SD, which is normally
only a few gigabytes, you can use an external drive to save the data. This section shows you how to tell
MySQL to change its default location for saving data.
The steps involved require stopping the MySQL server, changing its configuration, and then restarting
the server. Finally, you test the change to ensure that all new data is being saved in the new location. Begin
by stopping the MySQL server.
$ sudo /etc/init.d/mysql stop
You must create a folder for the new data directory.$ sudo mkdir /media/HDD/mysql
Now you copy the existing data directory and its contents to the new folder. Notice that you copy only
the data and not the entire MySQL installation, which is unnecessary.
$ sudo cp -R /var/lib/mysql/* /media/HDD/mysql
$ chown -R mysql mysql /media/HDD/mysql/