How to setup MySQL

Here are the tips I learned while setting up MySQL

1. # yum install mysql mysql-server
2. Edit /etc/my.cnf and enter datadir information (i.e /mysql/data)
3. Bring up /etc/init.d/mysqld start
4. Start MySQL server
5. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
6. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

Reference

 FullText

To index 3 charater words add the following to the /etc/my.cnf file at the end of the [mysqld] section.

ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Then restart the mysql service, and run the following sql.

mysql> repair table mw_searchindex quick;

Install and Configure

1. Edit /etc/my.cnf and enter Data File Location (datadir) information (i.e /mysql/data)
   • [mysqld] datadir=/my
2. Bring up /etc/init.d/mysqld start
3. Start MySQL server
4. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
5. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

 Change Root Password

mysqladmin -u root password NEWPASSWORD mysqladmin -u root -p oldpassword newpass

Create Database

The following are the instructions on creating database using mysql client

$ mysql -u adminusername -p

Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5340 to server version: 3.23.54
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE databasename;

Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname"-> IDENTIFIED BY "password";

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye

$

 phpMyAdmin

1. Install phpadmin under /opt/www (untar package from /opt/downloads). Rename original phpadmin name to phpMyAdmin.
2. On Ubuntu type: sudo apt-get install phpmyadmin

SQL Developer

SQL Developer can be installed on Linux or Windows, and now supports not only Oracle but also MySQL and MSSQL

1. Download SQL Developer from Oracle
2. To install third party database drivers: start SQL Developer, choose help check for updates, third party

Change Users Password

mysql -u adminusername -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5340 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

mysql> FLUSH PRIVILEGES;

Backup and Restore

 Setup mySQL backup scripts

Scripts based on work found here

1. Create a directory for the mysql backup scripts for example /opt/scripts
2. Create a directory for the mysql backup files for example /opt/db_backups
3. Create a file in the backup scripts directory named backup_dbs.sh, set up a cron entry to call this file.

#!/bin/sh
php /opt/scripts/backup_dbs.php

1. Create a file named backup_dbs_config.php in the scripts directory.
2. Create another file named backup_dbs.php also in the scripts directory.

 To restore a database type

• Use the this restore script Mysql Restore script or the steps below

1. tar -xvf <database_backup_file>
2. bunzip2 <database_backup_file>
3. Login to mysql as root mysql -u root -p
4. Drop the database (If the destination for restore already exists): mysql> drop database wikidb;
5. Logout of mysql
6. Create the databasemysqladmin -uroot -p<passwd>create wikidb
7. mysql -uroot -p<password> wikidb < wikidb.sql
8. restore the files from images and uploads directories