#Apache httpd.conf tweak: prefork.c module

#Add this to httpd.conf

=====================

<IfModule prefork.c>

StartServers 15

MinSpareServers 10

MaxSpareServers 40

ServerLimit 256Max

Clients 256

MaxRequestsPerChild 1000

</IfModule>

=====================

#mysql tweak:

#add this to my.cnf

=====================

key_buffer = 48

Mmax_allowed_packet = 8M

table_cache = 128

sort_buffer_size = 48M

net_buffer_length = 8M

thread_cache_size = 4

query_cache_type = 1

query_cache_size = 4M

=====================

#Another mysql tweak:

#add to my.cnf

max_connections = 200

bind-address = 127.0.0.1

safe-show-database

skip-locking

skip-innodb

# MySQL 4.x has query caching available.

# Enable it for vast improvement and it may be all you need to tweak.

query_cache_type=1

query_cache_limit=1M

query_cache_size=32M

interactive_timeout=100

# Reduced wait_timeout to prevent idle clients holding connections.

wait_timeout=15

connect_timeout=10

# Checked opened tables and adjusted accordingly after running for a while.

table_cache=512

# Reduced it to 32 to prevent memory hogging.

thread_cache=32

# Reduced it by checking current size of *.MYI

files.key_buffer=128M

thread_concurrency=1

log_error                = /var/log/mysql/error.log

# log slow queries is a must.

log_slow_queries=/var/log/mysqld.slow.log

long_query_time=2

[mysqldump]

quick

max_allowed_packet=16M

[mysql]

no-auto-rehash

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[mysqlhotcopy]

interactive-timeout

=====================

To show the granted permissions for a MySQL username, first you will need to know the username that you need to check on. In this article, the username will be test_db. So, to show the information for the user, you will first drop into the mysql shell.

root@server [/]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0001
Server version: 5.0.91-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Then, you use the show grants command to show the granted permissions for that MySQL user.

mysql> show grants for ‘test_db’@'localhost’;
+————————————————————————————————————————————————————————————————–+
| Grants for test_db@localhost |
+————————————————————————————————————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘test_db’@'localhost’ IDENTIFIED BY PASSWORD ‘*DYC75DW0KD63D82OEGMCF334LKSUTAQQLUDRW’ |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE ON `test\_db`.* TO ‘test_db’@'localhost’ |
+————————————————————————————————————————————————————————————————–+
2 rows in set (0.00 sec)

You can tell the database that it it granted the permissions to from this part of that statement, where test\_db is the database test_db.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE ON `test\_db`.* TO ‘test_db’@'localhost’ |

If you need to find out the available storage engines for MySQL, such as InnoDB, you can run the following command.

mysql -Bse 'show engines'

That will output the engines and their availability, and will look something like this.

root@server [~]# mysql -Bse ‘show engines’
MyISAM  DEFAULT Default engine as of MySQL 3.23 with great performance
MEMORY  YES     Hash based, stored in memory, useful for temporary tables
InnoDB  YES     Supports transactions, row-level locking, and foreign keys
BerkeleyDB      YES     Supports transactions and page-level locking
BLACKHOLE       NO      /dev/null storage engine (anything you write to it disappears)
EXAMPLE NO      Example storage engine
ARCHIVE NO      Archive storage engine
CSV     NO      CSV storage engine
ndbcluster      NO      Clustered, fault-tolerant, memory-based tables
FEDERATED       NO      Federated MySQL storage engine
MRG_MYISAM      YES     Collection of identical MyISAM tables
ISAM    NO      Obsolete storage engine

If you forget the login to the WordPress dashboard and the “forgot your password” link isn’t working for you, it is possible to change it from the command line. First, you will want to find the database that WordPress is using, so go to the folder that holds it, and get that from the wp-config.php file.

#]grep -i db wp-config.php

That will show you the database information from that file. It will come out like this.  All that you will need for what we are doing is the database name, which is db_name.

root@server [/home/user/public_html]# grep -i db wp-config.php
define(‘DB_NAME’, ‘db_name’);
define(‘DB_USER’, ‘db_user’);
define(‘DB_PASSWORD’, ‘yourdbpw’);
define(‘DB_HOST’, ‘localhost’);
define(‘DB_CHARSET’, ‘utf8′);
define(‘DB_COLLATE’, ”);

Then, you will need to drop into the mysql command line using that database.

#]mysql db_name

After that, you will want to list the users that are in the database to get some of the information that you will need to change the password.

mysql>select * from wp_users;

That will show the users and the ids for the users, which will look something like this.

+—-+————+————————————+—————+—————————+————————-+———————+———————+————-+————–+
| ID | user_login | user_pass                          | user_nicename | user_email                | user_url                | user_registered     | user_activation_key | user_status | display_name |
+—-+————+————————————+—————+—————————+————————-+———————+———————+————-+————–+
|  1 | Admin      | dOInlojhdE93LpD/kdiascxpItw33Ld/ | admin         | admin@yoursite.com |                         | 2011-01-06 08:08:52 |                     |           0 | Admin        |
|  2 | user2      | dOInlojhdE93LpD/kdiascxpItw33Ld/ | user2         | user2@secondsite.com        | http://www.yoursite.com | 2011-01-11 22:27:31 |                     |           0 | User2        |
+—-+————+————————————+—————+—————————+————————-+———————+———————+————-+————–+
2 rows in set (0.00 sec)

Finally, it’s time to change the password. You will use the id number in the first column of the user you need to change in this command. If we are changing the user admin with the id number 1, we would use this.

mysql> update wp_users set user_pass = MD5('password') where ID = 1;

Now, you should be able to log into the dashboard with the username and whatever you put in the place of password.

You can delete a mysql database by going into the mysql command prompt and using the drop database command.  Therefore, if you have a database named data_base that you would like do delete, you can do the following:

#mysql
mysql>drop database data_base;

Then, to recreate that database, use the create database command

mysql>create database data_base;

You are able to use the command line to restore a database using the mysql command.  To restore a file named backup.sql to the database data_base, first make sure that the database is empty, then you can run

mysql -o data_base < backup.sql 

It is good to use mysql -o when restoring the database, which makes sure to only affect the database that you specify in the command.