mysqld –help

This is useful on production servers.

By default MySQL treats updates as higher priority operations. You can use SELECT HIGH_PRIORITY or UPDATE LOW_PRIORITY to adjust that or you can simply set low_priority_updates option. Anyway default behavior means any UPDATE statement which is blocked by long running select will also block further selects from this table – they will have to wait until UPDATE is executing which is waiting on SELECT to complete. This is often not accounted for and people think – “OK. I write my script so it does short updates so it will not block anything” – it still may cause total block if there are long selects running.

low_priority_updates=1
concurrent_insert=2

MySQL
The server is writing a packet to the network.

SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB"
FROM information_schema.TABLES GROUP BY table_schema;

Edit /etc/my.cnf and locate line that reads as follows
[mysqld]

Make sure line skip-networking is commented (or remove line) and add the following line:
bind-address=[YOUR.SERVER.IP.ADDRESS]

For example, if your MySQL server IP is 10.10.10.1 the config should look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 10.10.10.1
# skip-networking

Also make sure you have access from outside for that user:
GRANT USAGE ON *.* TO '[User1]'@'%' IDENTIFIED BY '[password]';

Stop mysql services:

/etc/init.d/mysql stop

Start mysql in safe mode and skip user authentication:
mysqld_safe --skip-grant-tables &

Login to mysql as root, but you need use other console for this:
mysql -u root

Set a new password:
UPDATE mysql.user SET Password=PASSWORD('new_pass') WHERE User='root';

When you modify the grant tables manually you need to run:
flush privileges;

Restart the mysql services:
/etc/init.d/mysql start

Show users
SELECT user, host, password FROM mysql.user;

Create new user accessible only on localhost
CREATE USER '[User1]'@'localhost' IDENTIFIED BY '[password]';

Create new user accessible from anywhere
CREATE USER '[User1]'@'%' IDENTIFIED BY '[password]';

Grant permissions to all databases for User1 from localhost only
GRANT ALL PRIVILEGES ON *.* TO 'User1'@'localhost' WITH GRANT OPTION;

Grant permissions to specified database for User1 from anywhere
GRANT ALL PRIVILEGES ON [db_name].* TO 'User1'@'%' WITH GRANT OPTION;

Create a new user and grant permissions to database at once
GRANT ALL PRIVILEGES ON [db_name].* To 'user'@'hostname' IDENTIFIED BY '[password]';

Show users permissions
SHOW GRANTS FOR [username]@localhost;

Show MySQL uptime:
mysqladmin version

Login to MySQL
mysql -h [hostname] -u [username] -p

Show databases
show databases;

Create database
create database [name];

Delete database
drop database [name];

Use database
use [database];

Show tables
show tables;

Show columns
show columns from [table];

Find and replace string
update [table_name] set [column_name] = replace([column_name],'[string_to_find]','[string_to_replace]');

To backup and restore a mysql database see one of my earlier posts here:
http://www.homecomputerlab.com/backing-up-mysql-databases

Working with MySQL Database

Create a database with a specified name if it does not exist in database server

Use database or change current database to another database you are working with

Drop a database with specified name permanently. All physical file associated with the database is no longer exists.

Show all available databases in the MySQL database server

Working with Table

Lists all tables in a database.

Create table statement that defines the structure of table .

Altering table structure

Actions can be one of the following actions:

Add a new column into a table

Drop an existing column in a table

Add index with a specific name to a table on a column

Add primary key into a tables

Remove primary key from a table

Deleting table permanently

Get information about the table or column.

Working with Index

Creating an index with the specified name on a table

Removing a specified index from table

Querying Data

Query complete data in a database table

Query specified data which is shown in the column list from a database table

Query unique records

Query data from multiples table using join

Counting number of rows in a database table

Sorting ascending or descending based on one or more columns:

Group the retrieved rows data:

Matching Data based on a pattern

Matching data using LIKE operator:

Matching data using regular expression

The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

this error means that too many files are open for the given process. There is a read-only mysql variable called open_files_limit that will show how many open files are allowed by the mysql server.

SHOW VARIABLES LIKE 'open%'

A lot systems set this to something very low, like 1024. Unfortunately, the following will NOT work:

SET open_files_limit=100000

MySQL will respond with:

ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable

However, it is possible to make a change to /etc/my.cnf. This file may not exist, if not, just create it. Be sure it has the following contents:

[mysqld]
open_files_limit = 100000

Then, be sure to restart mysql:

sudo /etc/init.d/mysql restart

Now, SHOW VARIABLES LIKE 'open%' should show 100000. The number you use may be different.