How to Check and Repair MySQL Tables Using Mysqlcheck

When your mysql table gets corrupted, use mysqlcheck command to repair it.

Mysqlcheck command checks, repairs, optimizes and analyzes the tables.

1. Check a Specific Table in a Database

If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.

The following example checks employee table in thegeekstuff database.

# mysqlcheck -c thegeekstuff employee -u root -p

Enter password:

thegeekstuff.employee    OK

You should pass the username/password to the mysqlcheck command. If not, you’ll get the following error message.

# mysqlcheck -c thegeekstuff employee

mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

Please note that myisamchk command that we discussed a while back works similar to the mysqlcheck command. However, the advantage of mysqlcheck command is that it can be executed when the mysql daemon is running. So, using mysqlcheck command you can check and repair corrupted table while the database is still running.

2. Check All Tables in a Database

To check all the tables in a particular database, don’t specify the table name. Just specify the database name.

The following example checks all the tables in the alfresco database.

# mysqlcheck -c alfresco  -u root -p

Enter password:

alfresco.JBPM_ACTION                               OK

alfresco.JBPM_BYTEARRAY                            OK

alfresco.JBPM_BYTEBLOCK                            OK

alfresco.JBPM_COMMENT                              OK

alfresco.JBPM_DECISIONCONDITIONS                   OK

alfresco.JBPM_DELEGATION                           OK

alfresco.JBPM_EVENT                                OK

..

3. Check All Tables and All Databases

To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.

# mysqlcheck -c  -u root -p --all-databases

Enter password:

thegeekstuff.employee                              OK

alfresco.JBPM_ACTION                               OK

alfresco.JBPM_BYTEARRAY                            OK

alfresco.JBPM_BYTEBLOCK                            OK

..

..

mysql.help_category

error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!

mysql.help_keyword

error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!

..

If you want to check all tables of few databases, specify the database names using “–databases”.

The following example checks all the tables in thegeekstuff and alfresco database.

# mysqlcheck -c  -u root -p --databases thegeekstuff alfresco

Enter password:

thegeekstuff.employee                              OK

alfresco.JBPM_ACTION                               OK

alfresco.JBPM_BYTEARRAY                            OK

alfresco.JBPM_BYTEBLOCK                            OK

..

4. Analyze Tables using Mysqlcheck

The following analyzes employee table that is located in thegeekstuff database.

# mysqlcheck -a thegeekstuff employee -u root -p

Enter password:

thegeekstuff.employee   Table is already up to date

Internally mysqlcheck command uses “ANALYZE TABLE” command. While mysqlcheck is executing the analyze command the table is locked and available for other process only in the read mode.

5. Optimize Tables using Mysqlcheck

The following optimizes employee table that is located in thegeekstuff database.

# mysqlcheck -o thegeekstuff employee -u root -p

Enter password:

thegeekstuff.employee         OK

Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

6. Repair Tables using Mysqlcheck

The following repairs employee table that is located in thegeekstuff database.

# mysqlcheck -r thegeekstuff employee -u root -p

Enter password:

thegeekstuff.employee        OK

Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.

7. Combine Check, Optimize, and Repair Tables

Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.

The following checks, optimizes and repairs all the corrupted table in thegeekstuff database.

# mysqlcheck -u root -p --auto-repair -c -o thegeekstuff

You an also check, optimize and repair all the tables across all your databases using the following command.

# mysqlcheck -u root -p --auto-repair -c -o --all-databases

If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.

# mysqlcheck --debug-info -u root -p --auto-repair -c -o thegeekstuff employee

Enter password:

thegeekstuff.employee  Table is already up to date

User time 0.00, System time 0.00

Maximum resident set size 0, Integral resident set size 0

Non-physical pagefaults 344, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 12, Involuntary context switches 9

8. Additional Useful Mysqlcheck Options

The following are some of the key options that you can use along with mysqlcheck.

§  -A, –all-databases Consider all the databases

§  -a, –analyze Analyze tables

§  -1, –all-in-1 Use one query per database with tables listed in a comma separated way

§  –auto-repair Repair the table automatically it if is corrupted

§  -c, –check Check table errors

§  -C, –check-only-changed Check tables that are changed since last check

§  -g, –check-upgrade Check for version dependent changes in the tables

§  -B, –databases Check more than one databases

§  -F, –fast Check tables that are not closed properly

§  –fix-db-names Fix DB names

§  –fix-table-names Fix table names

§  -f, –force Continue even when there is an error

§  -e, –extended Perform extended check on a table. This will take a long time to execute.

§  -m, –medium-check Faster than extended check option, but does most checks

§  -o, –optimize Optimize tables

§  -q, –quick Faster than medium check option

§  -r, –repair Fix the table corruption

Trigger mysql example. Introduction on Mysql trigger

How to do the data validation directly from MySQL? Trigger mysql example.

Many times I wanted to do data validation directly when inserting into a MySQL table and leave the source code of my application nice, clean and very readable, but support for that was only in Oracle and other SQL servers but not in MySQL.

Beginning with version 5 of MySQL the development team has added “triggering” to their SQL server engine, to meet the growing demand of users.

But what are triggers ?

A trigger is a database object that is associated with a table and is activated when an event occurs for that table. Can be successfully used for database data validation and any other calculations done directly on the database.

When is a trigger activated ?

A trigger is activated when changes are made on that table where it is associated with, more precisely on Insert/Update/Deleted execution statements
A simple example:

view sourceprint?

01.-- Firstly we create a test table

02.CREATE TABLE test (id INT, sum DECIMAL(10,2));

03.-- now we create the trigger for that table:

04.delimiter//

05.create trigger insert_trg before insert on test

06.for each row

07.begin

08.

if new.sum<0 then

09.

set new.sum=0;

10.

endif;

11.end//

12.delimiter;

What is this trigger doing ?

It is a simple field validation.

When we will try to insert into the table (ex insert into test values (1,-3)) this field sum will always be greater than 0 because we change negative values to 0.

How it works:

Create trigger is creating the trigger with the name insert_trg


The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. You can use the AFTER keyword to activate it after each inserted row in the table.

The Insert keyword stands for the action what will activate the trigger. You can also use Update or Delete.

For each row means this trigger will be activated for each inserted action.

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used as there is no old row. In a DELETE trigger, only OLD.col_name can be used as there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before they were updated and NEW.col_name to refer to the columns of the row after they were updated.



By using the BEGIN … END construct you can define a trigger that executes multiple statements.



How can a trigger be removed?

Just execute command: Drop trigger trigger_name.



Conclusion

I hope this little article will prove helpful for those with prior experience in MySQL but who haven’t studied its new features

Mysqladmin - Client for Administering a MySQL Server

1. How to change the root password for Mysql?

mysqladmin -u root -ptmppassword password 'newpassword'

# mysql -u root -pnewpassword

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

2. How to check whether the MySQL server is running using mysqladmin?

# mysqladmin -u root -p ping

Enter password:

mysqld is alive

3. How do I see what version of MySQL is running using mysqladmin?

This command also displays the current status of the server.

# mysqladmin -u root -ptmppassword version

mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686

Copyright (C) 2000-2006 MySQL AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

Server version 5.1.25-rc-community

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 107 days 6 hours 11 min 44 sec

Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067

Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

4. How to see the status of the MySQL server using mysqladmin?

# mysqladmin -u root -ptmppassword status

Uptime: 9267148

Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067

Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

Status command displays the following informatsiyucommand displays the following information:

• Uptime: uptime in seconds

• Threads: Total number of clients connected to the server.

• Questions: The total number of requests to the server since startup.

• Slow queries: Total number of queries, whose execution time was more than the value of long_query_time.

• Opens: Total number of tables opened by the server.

• Flush tables: How many times the tables were flushed.

• Open tables: Total number of open tables in the database.

5. How do I view MySQL status variables and their current value using mysqladmin?

# mysqladmin -u root -ptmppassword extended-status

+-----------------------------------+-----------+
Variable_name Value
+-----------------------------------+-----------+

Aborted_clients  579
Aborted_connects 8
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 41387238
Bytes_sent 308401407
Com_admin_commands 3524
Com_assign_to_keycache 0
Com_alter_db 0
Com_alter_db_upgrade 0

6. How to display all the MySQL server system variables and their values using mysqladmin?

# mysqladmin -u root -ptmppassword variables

+---------------------------------+---------------------------------+
Variable_name Value
+---------------------------------+---------------------------------+
auto_increment_increment 1
basedir /
big_tables OFF
binlog_format MIXED
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_database latin1
character_set_filesystem binary
skip.....
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmpdir /tmp
tx_isolation REPEATABLE-READ

+--------------------------------+---------------------------------+

7. How to display all running processes / queries in the database mysql using mysqladmin?

# mysqladmin -u root -ptmppassword processlist

+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
20 root localhost Sleep 36
23 root localhost Query 0 show processlist
+----+------+-----------+----+---------+------+-------+------------------+

You can use this command to effectively debug any performance problems and determine the process that causes problems by running on autoupdate every second.

# mysqladmin -u root -ptmppassword -i 1 processlist

+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
20 root localhost Sleep 36
23 root localhost Query 0 show processlist
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
24 root localhost Query 0 show processlist
+----+------+-----------+----+---------+------+-------+------------------+

8. How to create database a MySQL using mysqladmin?

# mysqladmin -u root -ptmppassword create testdb

# mysql -u root -ptmppassword

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 705

Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+--------------------+
Database
+--------------------+
information_schema
mysql
sugarcrm
testdb
+--------------------+

4 rows in set (0.00 sec)

Note: To display all the tables in the database, the total number of columns, rows, indexes and so on .... use the command mysqlshow.

9. How do I delete the existing database MySQL using mysqladmin?

# mysqladmin -u root -ptmppassword drop testdb

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'testdb' database [y/N] y

Database “testdb” dropped

# mysql -u root -ptmppassword

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 707

Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;

+——————–+
Database
+——————–+
information_schema
mysql
sugarcrm
+——————–+
3 rows in set (0.00 sec)

10. How to overload all the privileges and rights to the table using mysqladmin?

# mysqladmin -u root -ptmppassword reload;

Refresh command resets the table and close / open the log files.

# mysqladmin -u root -ptmppassword refresh

11. How to perform a safe way to stop MySQL server using mysqladmin?

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword

ERROR 2002 (HY000): Can't connect to local MySQL server

through socket '/var/lib/mysql/mysql.sock'

You can use the command "/ etc / rc.d / init.d / mysqld stop" to stop the server. To start the run "/ etc / rc.d / init.d / mysql start"

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts

# mysqladmin -u root -ptmppassword flush-logs

# mysqladmin -u root -ptmppassword flush-privileges

# mysqladmin -u root -ptmppassword flush-status

# mysqladmin -u root -ptmppassword flush-tables

# mysqladmin -u root -ptmppassword flush-threads

• Flush-hosts: Reset all the information in the cache hosts.

• Flush-privileges: Reload right.

• Flush-status: Clear status variables.

• Flush-threads: Flush the thread cache.

13. How to kill hung client process to MySQL using mysqladmin?

First, define the hanging process using the command processlist.

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
20 root localhost Sleep 64
24 root localhost Query 0 show processlist
+----+------+-----------+----+---------+------+-------+------------------+
Then use the kill command and the desired process_id. To complete several processes separate process id commas.

# mysqladmin -u root -ptmppassword kill 20

# mysqladmin -u root -ptmppassword processlist

+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
26 root localhost Query 0 Show processlist
+----+------+-----------+----+---------+------+-------+------------------+

14. How to start or stop the replication of MySQL on the slave-server using mysqladmin?

# mysqladmin -u root -ptmppassword stop-slave

Slave stopped

# mysqladmin -u root -ptmppassword start-slave

mysqladmin: Error starting slave: The server is not configured as slave;

fix in config file or with CHANGE MASTER TO

15. How to combine several commands mysqladmin together ?

In the example below combined team process-list, status and version for the complete withdrawal of the status of the server.

# mysqladmin -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+----+------+-----------+----+---------+------+-------+------------------+
43 root localhost Query 0 show processlist
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3135
Threads: 1 Questions: 80 Slow queries: 0 Opens: 15 Flush tables: 3

Open tables: 0 Queries per second avg: 0.25

mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686

Copyright (C) 2000-2006 MySQL AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

Server version 5.1.25-rc-community

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 52 min 15 sec

You can also use the short form of writing:

# mysqladmin -u root -ptmppassword pro stat ver

Use the-h option to connect to remote MySQL server and execute the command.

# mysqladmin -h 192.168.1.112 -u root -ptmppassword pro stat ver

Useful articles:

Mysqladmin is a client for performing administrative operations!

Shell script to backup a Mysql database and save it on a remote server using Ftp

The following shell script will dump the mysql database and will save the .sql file on a remote location using Ftp. This script will create a backup file including the current date so you can have multiple copies of the backups of the same database under one directory.
Create a file called mysqlbkup.sh

# vi /root/mysqlbkup.sh

and paste the following code in the file as it is.

##############START OF THE SCRIPT##############

#!/bin/bash

# Specify the temporary backup directory
BKUPDIR="/tmp"

# Database Name
dbname="dbname_here"

# store the current date
date=`date '+%Y-%m-%d'`

# Specify Ftp details
ftpserver="FtpServerIP"
ftpuser="username"
ftppass="password"

# Dump the mysql database with the current date and compress it.
#Save the mysql password in a file and specify the path below
/usr/bin/mysqldump -uroot -p`cat /path/to/passfile` $dbname | gzip > $BKUPDIR/$date.$dbname.sql.gz

# Change directory to the backup directory
cd $BKUPDIR

# Upload the backup
ftp -n $ftpserver <user $ftpuser $ftppass
binary
prompt
mput *.sql.gz
quit
!EOF!

# Remove the local backup file
/bin/rm -f /$BKUPDIR/$date.$dbname.sql.gz

##############END OF THE SCRIPT##############

Save the file and schedule a cronjob to execute the file on daily basis, say during night hours at 1.00AM. Edit the cron file

# crontab -e

and set the following cronjob

0  1  *  *  *  /bin/sh /root/mysqlbkup.sh

save the file and restart the crond service

# service crond service

The script will work on a Linux/Plesk server as well. You just have to replace the mysqldump line in the script with the following

/usr/bin/mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $dbname | gzip > $BKUPDIR/$date.$dbname.sql.gz

Make sure you assign the db_name, ftpserver/user/pass values properly at the start of the script.
Note: Leave a comment if you have any suggestions, questions OR have received any error message using this script.

How to defragment or optimize a database in Mysql?

In case you remove a lot of data from the tables OR change the database structure, a de-fragmentation/optimizing of the database is necessary to avoid performance loss, especially while running queries. The above changes results in a performance loss, so make sure you run the “optimizer” on the database.
SSH to your server and execute:

mysqlcheck -o 

where, -o stands for optimize which is similar to defragmentation. You should look to defragment the tables regularly when using VARCHAR fields since these coloumns get fragmented too often.

Enable MySQL General Query And Slow Query Log

MySQL has a query logging feature. In order to use it you have to first enable it.

Enabling the general query log

Step 1: Set your log file in /etc/my.cnf

vi /etc/my.cnf

In the [mysqld] section specify the general log file name:

log=/var/log/mysqld.general.log

Step 2: Create the file and make sure it is owned by the system user mysql

touch /var/log/mysqld.general.log
chown mysql.mysql /var/log/mysqld.general.lo

Step 3: Enable the general log in the MySQL client. Connect to the MySQL server using the MySQL client and execute this query.

SET GLOBAL general_log = 'ON';

Step 4: Restart mysqld and watch the logs

/etc/init.d/mysqld restart

To watch the logs use the tail command.

tail -f /var/log/mysqld.general.log

A sample entry in my general query log looks like:

[root@localhost ~]# 
[root@localhost ~]# tail -f /var/log/mysqld.general.log 
091012 13:52:53     2 Query SELECT `profile`.* FROM `profile` WHERE (user_id = 1) LIMIT 1
      2 Query SELECT `u`.`user_id`, `u`.`email`, `u`.`status`, `u`.`mode`, `u`.`hash`, `u`.`created`, `u`.`alt_email`, `u`.`host_created`, `u`.`ip_created`, `u`.`user_timezone`, `p`.*, `r`.`name` AS `primaryRoleName`, `urp`.`email` AS `reportsToEmail`, `b`.`branch_name` FROM `user` AS `u`
 LEFT JOIN `profile` AS `p` ON p.user_id = u.user_id
 LEFT JOIN `role` AS `r` ON r.role_id = p.primary_role
 LEFT JOIN `user` AS `urp` ON urp.user_id = p.reports_to
 LEFT JOIN `branch` AS `b` ON b.branch_id = p.branch_id WHERE (u.user_id = '1')
      2 Query SELECT `profile`.* FROM `profile` WHERE (user_id = 1) LIMIT 1
      2 Query SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices\"') LIMIT 1
      2 Query SELECT * FROM organization_details
      2 Quit 

Enabling the slow query log

Similarly, you can enable the slow log queries. MySQL reports how much time it took to execute a query.
Step 1: Enable slow log query, set a slow log query log file, and set the log_query_time in my.cnf.
Edit the MySQL configuration file.

vi /etc/my.cnf

In the [mysqld] section add/edit the following variables

long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld.slow.query.log

We are setting the log_query_time variable to 0. Any MySQL query taking more than 0 seconds will be logged. Step 2: Create the file /var/log/mysqld.slow.query.log and set the system user mysql as the owner.

touch /var/log/mysqld.slow.query.log
chown mysql.mysql /var/log/mysqld.slow.query.log

Step 3: Restart MySQL server

/etc/init.d/mysqld restart

Step 4: Watch the slow query log using the tail command.

tail -f /var/log/mysqld.slow.query.log

A sample entry on my server looks like this:

[root@localhost ~]# tail -f /var/log/mysqld.slow.query.log 
SET timestamp=1255345490;
SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices\"') LIMIT 1;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000273  Lock_time: 0.000104 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
SELECT * FROM organization_details;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000048  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
# administrator command: Quit;

If you observe the log entry, the last query took 0.000048 seconds.

How to enable ‘General Query Log’ in Mysql?

General Query Log is used to keep track of mysql status i.e. it writes the information when a client connects/disconnects OR a query is executed. It is useful when the number of people managing the database is high. In order to enable ‘General Query Log’,
edit the Mysql configuration file

vi /etc/my.cnf

enable the log under the ‘mysqld’ section

log=/var/log/mysql.general.log

Save the file. Now create the log file and set the mysql ownership

touch /var/log/mysql.general.log
chown mysql.mysql /var/log/mysql.general.log

Now, restart the mysql service

/etc/init.d/mysql restart

You can now execute the queries using phpMyAdmin OR 3rd party sql software and watch the logs

tail -f /var/log/mysql.general.log

Mysql commands ..

The following is a list of MySQL useful commands.

# To setup root password (first execution)

mysqladmin -u root password 'new_password'

# To login to MySQL

mysql -u root -p

# To create a database

create database dbname;

# To change database

use dbname;

# To create a user and assign to it permission to database

# Grant permission only from localhost connections

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Grant permission on all connections

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Manage MySQL export

# Copy all db1 content to /backup-db/db1 folder

mysqlhotcopy db1 /backup-db/db1

# Create a DB dump to a file

mysqldump db1 > db1_dump_db.sql -u root -p

Hope this help

Mysql Command ie used Most frequent.

This is a list of handy MySQL commands that I use frequently.
Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.
To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> show tables;
To see database’s field formats.
mysql> describe [table name];
To delete a db.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];
Show certain selected rows with the value “whatever”.
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name “Bob” AND the phone number ‘3444444′.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name “Bob” AND the phone number ‘3444444′ order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters ‘bob’ AND the phone number ‘3444444′.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Show all records starting with the letters ‘bob’ AND the phone number ‘3444444′ limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process.
Start again with no grant tables.
Login to MySQL as root.
Set new password.
Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db’s.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');