takizo, not takezo

By default Linux CentOS or other Linux Distro installation, MySQL data directory is stored in /var/db/mysql, how can I change it to other directory in example /db/mysql?

It is always better to have MySQL Data Directory store in a specific partition/drive. It will help on performance and better management and scalability. You change change the data store directory in Linux by editing /etc/my.cnf file.

Edit /etc/my.cnf file

# vi /etc/my.cnf

Change the data directory structure

datadir=/db/mysql
socket=/db/mysql/mysql.sock

After the file has been updated, restart MySQL service.

Sometime psql command line query can be useful when it comes to Bash script processing. There are data stored in PostgreSQL database and I would like to write a bash script to massage the data into report. Example below show query to PostgreSQL database by using the psql command.

/usr/local/bin/psql -c "SELECT email_address FROM members WHERE active is TRUE;" -U someuser -t -A -o member-email-address.txt accounting

psql is connecting to accounting database, query from members table and out the result into a txt file name member-email-address.txt.

-t – Return result only, do not result with table name.
-A – By default, returned result will have white space, this is to remove the white space.
-o – Output the result into a file.

By default, MySQL root privileges user is “root”, I always remove “root” userid once I got MySQL installed, mainly for security purpose, secondly I do not want stupid thing happened like someone able to brute force into MySQL database.

You can create “root” alike privilege user in MySQL by following the step below;

• Access to mysql /usr/local/bin/mysql
• mysql> GRANT ALL PRIVILEGES ON *.* TO ‘yourusername’@'localhost’ IDENTIFIED BY ‘yourpasswordhere’ WITH GRANT OPTION;
• mysql> flush privileges;
• mysql> quit;

You can add a few more privileges user if you have more than 1 person to admin MySQL Database.

Very often, once we didn’t log on to MySQL database for some time, I guess most of us will forgot the root password. Can we reset the root password? Obviously… Nothing is Impossible

Follow the steps below to reset MySQL’s Root Password;

• Stop MySQL Service
• Start MySQL Service in Safe Mode: /usr/local/bin/mysqld_safe –skip-grant-tables&
• Connect to MySQL /usr/local/bin/mysql
• mysql> use mysql;
• mysql> UPDATE user set password=password(‘newpassword’) where user=’root’ and host=’localhost’;
• mysql> flush privileges;
• mysql> quit;
• Stop MySQL Service Again
• Start MySQL Service in normal mode

Log in to MySQL with your newly created password.