MySQL

Installing MySQL

• tar xzvf mysql-4.1.13a.tar.gz
• cd mysql-4.1.13a
• ./configure \
  --prefix=/usr/mysql41 \
  --with-mysqlfs --with-isam \
  --with-mysqld-user=mysql \
  --with-example-storage-engine \
  --with-archive-storage-engine \
  --with-csv-storage-engine \
  --enable-thread-safe-client \
  --enable-local-infile
• make
• make install
  

Create mysql user and group

• groupadd mysql
• useradd -g mysql mysql

Create temporary and data directory

• mkdir /usr/mysql41/tmp
• mkdir /usr/mysql41/var

Initiate database

• /usr/mysql41/bin/mysql_install_db

Setting permissions

• chown -R mysql /usr/mysql41/var
• chgrp -R mysql /usr/mysql41
• chown -R mysql /usr/mysql41/tmp

Copy default config file

• cd support-files
• cp my-medium.cnf /usr/mysql41/var/my.cnf

Edit the configuration file and change the socket path for both client and server section.

• vi /usr/mysql41/var/my.cnf

Setting init script

• cp mysql.server /etc/init.d/mysql41
• chmod +x /etc/init.d/mysql41
• chkconfig --add mysql41

Set the path of my.cnf file in init script

• vi /etc/init.d/mysql41
  conf=/usr/mysql41/var/my.cnf

Start Mysql

• service mysql41 start

How to take full mysql backup using mysql dump

mysqldump --all-databases -u root -p > mysql.sql

Important: In Mysql 5 you may get this error "Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES". In this case use below command for mysql 5.

mysqldump --single-transaction --all-databases -u root -p > mysql.sql

IF you need to restore that backup to an old version of mysql then use below switch

--compatible=mysql40
or
--compatible=mysql323

Extracting a specific database backup from a large mysql dump file

Suppose you have a 100 GB mysql dump file mysql.sql.
First execute this command to get the line numbers of all "CREATE DATABASE " statements.

grep -n "CREATE DATABASE" mysql.sql

Output will be like...
113968:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase1`;
209784:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase2`;
904563::CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase3`;

We want to extract backup of MyDatabase2, It starts from line 209784 and goes up to line 904563. We can get that data by using below command.

csplit -k mysql.sql 209784 904563

This command will create 3 files with names like XX00,XX01,XX02. XX00 will have data from line 1 to 209784, XX02 will have data from line 209784 to 904563. And XX03 will have data from line 904563 to the end of file.

So XX01 is the backup we require and it can be restored with mysql command line.

Reset forgotten mysql root password (DONT DO IT IF you have PLESK installed)

-Stop the mysql
-Now start it with --skip-grant-tables

/usr/bin/mysqld_safe --skip-grant-tables &

-Now ...

mysql --user=root mysql
Press enter when it prompt for password. And after login run this ..

update user set Password=PASSWORD('new-password-here') WHERE User='root';

-Kill mysql and start it again with your usual start script (e.g /etc/init.d/mysql start).

Secure the mysql

(No matter how strong internal grants/permissions you have, PCI Approved vendors will fail your server if you have mysql running on a public port.)

Drop the test database

Dont forget to set the root password on newly installed mysql

Run mysql on local IP 127.0.0.1
For this edit the my.cnf file which can be in /etc or /etc/mysql depending on your distro. And under the [mysqld] section add this line, and restart mysql
bind-address = 127.0.0.1

IF you need to run public then Change the default mysql port
This can be tricky depending on your mysql installation type. But this can help.

Edit /etc/my.cnf or /etc/mysql/my.cnf and under the section [mysqld] change the port in below line.
port = 3306

Also change same line in same file under [mysql] section so that your command line client can connect at that port by default. Then restart mysql.