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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5340 to server version: 3.23.54Type '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> EXITBye$
 phpMyAdmin

1. Install phpadmin under /opt/www (untar package from /opt/downloads). Rename original phpadmin name to phpMyAdmin.

Change Users Password
mysql -u adminusername -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5340 to server version: 3.23.54Type '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/shphp /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