Articles

1. How to install and setup MySQL from source code

2. Bash script for backup and restore of MySQL database

3. How to store mysql data in UTF-8 encoding

1. How to install and setup MySQL from source code


0. Download mysql-boost-VERSION.tar.gz (download tar with boost mentioned in its name )
1. groupadd mysql
2. useradd -r -g mysql -s /bin/false mysql

# Beginning of source-build specific instructions

3. tar zxvf mysql-boost-VERSION.tar.gz
4. cd mysql-VERSION
5. mkdir bld
6. cd bld
7. cmake .. -DENABLE_DOWNLOADS=1 -DWITH_BOOST=../boost
8. make
9. sudo make install

# End of source-build specific instructions

# Post Installation setup

10. cd /usr/local/mysql
11. sudo mkdir mysql-files
12. sudo chown mysql:mysql mysql-files
13. sudo chmod 750 mysql-files
14. bin/mysqld --initialize --user=mysql {this will generate a root password}
15. bin/mysqld_safe --user=mysql &
16. sudo cp support-files/mysql.server /etc/init.d/mysql.server
17. mysql -u root -p
18. enter generated password
19. ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-root-password';
20. check mysql working fine with new password
    mysql -u root -p
    new-root-password (Enter the new password)

2. Bash script for backup and restore of MySQL database


#!/bin/bash

DATE=(date +%d-%m-%Y)
DATE_DIR="DB_BACKUP_$(date +%d-%m-%Y_%H%M)"

/bin/mkdir -p $DATE_DIR

DB_NAME="dbname"
DB_LOGIN="dblogin"
DB_PASSWD="dbpassword"
DB_SCHEMA_FILE="dbschema.sql"
DB_FILE="dbbackup.sql"

TMPFILE="tmp.sql"
DUMMYFILE="dummy.sql"

echo "====== Taking backup of $DB_SCHEMA_FILE for $DB_NAME ======="
mysqldump  --single-transaction  --no-tablespaces -u$DB_LOGIN -p$DB_PASSWD --no-data $DB_NAME > $TMPFILE
grep -vw SET $TMPFILE | grep -v \- > $DUMMYFILE
awk 'NF > 0' $DUMMYFILE > $DB_SCHEMA_FILE
mv $DB_SCHEMA_FILE $DATE_DIR

echo "===============Taking backup of $DB_NAME =============="
mysqldump  --single-transaction  --no-tablespaces -u$DB_LOGIN -p$DB_PASSWD $DB_NAME > $DB_FILE
mv $DB_FILE $DATE_DIR

echo "===============Restore backup of $DB_NAME =============="
mysql -u$DB_LOGIN -p$DB_PASSWD $DB_NAME  < ./$DATE_DIR/$DB_FILE

echo "===============Restore backup of $DB_NAME into  NEW_DB_NAME =============="
mysql -u$NEW_DB_LOGIN -p$NEW_DB_PASSWD $NEW_DB_NAME  < ./$DATE_DIR/$DB_FILE

rm $DUMMYFILE
rm $TMPFILE

3. How to store mysql data in UTF-8 encoding


1. Create database (using cpanel / commands)
2. alter database databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci