MySQL HOWTOs

From Zedomax Wiki

Contents 1 How to Restart your MySQL server 2 Optimizing your MySQL 3 Let's edit the my.cnf file 4 Backing up your MySQL database with an automatic script and cron job 5 Optimize HTTPD Service 6 Optimize MySQL perfomance 7 Backing up MySQL database using command line 8 Restoring MySQL database using command line 9 How to Connect to MySQL using command line 10 Command to see the stuff in the database 11 Choose which table to use 12 see the tables 13 view what's in a table

How to Restart your MySQL server

Do this:

/sbin/service mysqld restart

Optimizing your MySQL

Once in awhile, after major changes to the database such as deletion large amount data, optimizize your MySQL database by running the following command: (This is like defragmenting your harddrive...)

mysqlcheck -o DatabaseName -uUserName -pPassword

Let's edit the my.cnf file

Here's some additions you might try that might help your MySQL database run faster:

query_cache_type = 1
query_cache_size = 26214400
set-variable = max_connections=1000
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=64M
set-variable = tmp_table_size=8M
set-variable = wait_timeout=120

Backing up your MySQL database with an automatic script and cron job

Okay, I've been running dedicated servers for couple years now and I used to mess around with Linux boxes back in college.

Here's an essential automatic script and a cron job you will need in order to keep your backup automatic.

So all you have to do is FTP your backup files.

This is way better than going into your phpMyAdmin and downloading from the web interface since the file is already ready for you to download.

Here's how you do it:

1) Make a new file called backup.sh in your home directory. 2) Use the following syntax:

mysqldump -u[USERNAME] -p[PASSWORD] --opt [DATABASE NAME] > /root/backup/DATABASE1.sql

Here's an example backup.sh file if you had 2 databases:

mysqldump -uUser1 -pPassword1 --opt Database1 > /root/backup/Database1.sql
mysqldump -uUser2 -pPassword2 --opt Database2 > /root/backup/Database2.sql
tar cvf /root/backup/sqlbackup.tar /root/backup/*

You can test your script by typing this:

sh backup.sh

(The mysqldump will overwrite the older file if you run this more than once)

3) If everything is working good, now you can put that script to be run once a day in your cron job.

For Fedora Core, you can edit this file /etc/cron.d/sa-update

Add the following line:

10 3 * * * root sh /root/backup/backup.sh

This will execute the script once a day at 3:10AM.

Now simply download one tar ball file whenever you feel like backing up your database.

You can probably change this a little bit and use it for many other things like backing up all your files too. (which I do)

Optimize HTTPD Service

Use the following settings for /etc/httpd/conf/httpd.conf:

Timeout 10
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 3

Optimize MySQL perfomance

Add the following lines to your /etc/my.cnf and restart the httpd service:

# HACK
query_cache_type = 1
query_cache_size = 26214400
set-variable = max_connections=200

Backing up MySQL database using command line

mysqldump -u [USERNAME] -p[PASSWORD] --opt [db_name] > backup.sql

Restoring MySQL database using command line

mysql -u [USERNAME] -p[PASSWORD] [db_name] < backup.sql

How to Connect to MySQL using command line

mysql --user=username --password=password database name

Command to see the stuff in the database

show databases;

Choose which table to use

use table

see the tables

show tables

view what's in a table

SELECT * FROM page;