Category MySQL

Tweaks for linux/freebsd apache and mysql – control the load with these tweaks

#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

quer...

Read More

Show Grants for a MySQL User

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 ...

Read More

Check the storage engines for MySQL

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 engi...

Read More

Change WordPress password from the command line

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 com...

Read More

Delete and recreate a MySQL database

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;
Read More

Restore a MySQL database from a .sql file

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.

Read More