Moving MySQL datafiles

In this article, I’ll demonstrate multiple solutions to move datafiles

Method 1: using symbolic links

Stop the Mysql instance.

/etc/init.d/mysql stop

Then move the files and put symbolic links to lure Mysql into thinking the files are still in place.

mkdir /new_dir/datafiles/my_db
cd /old_dir/datafiles/
cp -Rvp my_db/* /new_dir/datafiles/my_db/.
rm my_db/* && rmdir my_db/
ln -s /new_dir/datafiles/my_db/ my_db

Method 2: changing the configuration file

Open your Mysql configuration file: /etc/mysql/my.cnf and look for the entry for “datadir”. Then, simply change the path (which should be “/var/lib/mysql”) to the new data directory.

This method also requires an instance restart.

sudo /etc/init.d/mysql restart

Tags:

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

### PROBLEM On a **MySQL 5.6** database server, the *ibdata1* file includes 5 InnoDB tables in the mysql schema. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more

20 Jun 2016 - 1 minute read

Convert FLAC to mp3 like a hacker

#system #linux

So you need to convert a bonch of FLAC files to mp3 and your best search engine only recommands you online file converters that nobody trusts ore require a paid subscription to work. Don't worry, *there's always a way*, and even better, a true ***hacker style*** way to achieve this! This procedure works for MacOS through Brew, but you should b... Read more

20 Feb 2019 - less than 1 minute read

Blockchain and IoT

#blockchain #linux

Here's a small script to open a JavaScript interface to interact with an IoT device. This interface can then be easily incorporated in a larger Blockchain project with real tangible everyday life objects. I'll cover the Blockchain aspect of this project in another article, focusing on *Hyperledger*. The IoT device I'll be using for this project... Read more

20 Aug 2017 - 2 minute read

Image compression from the command line

#linux

Here's a small script to compress JPEG images in a folder. Useful to save a few kilobytes of bandwith when serving images from your website. I might improve this script in the future to include more compression tools and/or file extensions. ```bash #!/bin/bash for f in *.jpg *.JPG *.jpeg *.JPEG do echo -en "Converting ${f} ... " kb_or... Read more

20 Jul 2017 - less than 1 minute read