Mysql useful production query script

How to start MySQL
# service mysqld start
# /etc/init.d/mysqld start
How to set root password in MySQL
# mysqladmin password “redhat”
How to Login in Mysql
# mysql -u root -predhat
mysql>

How to change root and Users password
mysql> UPDATE mysql.user SET Password=PASSWORD(‘redhat123′) WHERE User=’root’;

How to Create database
mysql> create database DB-NAME;

How to Check database
mysql> show databases;

How to Create User
mysql> CREATE USER ‘USER_NAME’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

How to given PRIVILEGES
mysql> GRANT ALL PRIVILEGES ON `DB-NAME` . * TO ‘USER_NAME’@’localhost’;

How to given one Privileges
mysql> GRANT SELECT, INSERT, DELETE ON database TO username@’localhost’ IDENTIFIED BY ‘password’;

How to delete User
mysql> drop user ‘USER_NAME’@’localhost’;
——————————————————————————————
How to create MySQL data file in Another Location# mysql_install_db –user=mysql –datadir=”your data Location path ” (example –datadir=/var/tmp/abc)

How to Select DB
mysql> use DB-NAME;

How To Create Table in DB defaults Engine
mysql> CREATE TABLE TABLE_NAME (name varchar(50), mobile varchar(50) , id varchar(50) , remark varchar(50));
How to check table Schema
mysql> desc TABLE_NAME;

How to Check Table ENGINE
mysql> show create table TABLE_NAME;

How To check MySQL ENGINES
mysql> show engines;

How to check data in TABLE
mysql> select * from TABLE_NAME;

—————————————————————————————————-
How to Import CSV File in MySQL
mysql> LOAD DATA INFILE’/path/abc.csv’ INTO TABLE TABLE_NAME FIELDS TERMINATED BY ‘,’ ;

How to Create Index in Coloumn
mysql> ALTER TABLE table_Name ADD INDEX (coloumn_name);
Query OK, 0 rows affected (1 min 35.39 sec)
Records: 0 Duplicates: 0 Warnings: 0

How to Check Cretaed INDEX
mysql> show index from table_Name;

How to check user privileges
mysql > SHOW GRANTS FOR ‘root’@’localhost’;

How to set REPLICATION privileges (Master)
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘username’@’%’ IDENTIFIED BY ‘redhat’;

How to set Master Podition (Slave)
mysql> CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’username’,MASTER_PASSWORD=’redhat’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;

How to Skip Slave Error in one time
mysql> stop slave; SET GLOBAL sql_slave_skip_counter = 1; start slave;

How to read mysqlbin log File
# mysqlbinlog –base64-output=DECODE-ROWS –verbose replication.383626 > /tmp/replication1111.383626.txt

How to check mysql running binlog_format
mysql> SHOW VARIABLES like ‘%binlog_format%’;

How to Change Binlog format (ROW | MIXED | STETMENT )
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;
===============================================================
mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;

# my.cnf: binlog_format=ROW

How to check PROCEDURE and FUNCTION
mysql> SHOW PROCEDURE STATUS;
mysql> SHOW FUNCTION STATUS;

mysql> SHOW CREATE PROCEDURE PROCEDURE_NAME;
mysql> SHOW CREATE FUNCTION FUNCTION_NAME;

MySQL Error Code: 1548 Cannot load from mysql.proc. The table is probably corrupted
# mysql_upgrade -uroot -p –force

——————————
How to take mysql dump
—————————

How to take dump with Procedures and Functions
E:\bin>mysqldump.exe -u root -p –routines –verbose –single-transaction –no-data misdata > E:\DUMP.SQL\routines1.sql

How to take complete dump
# mysqldump -u root -p –all-database > /tmp/dump.sql

How to take dump only specific databases# mysqldump -uroot -p db1 db2 db3 > dump.sql

How to take dump only specific table in database
# mysqldump -u root -p database_Name table_Name > dump.sql

How to restore Dump
# mysql -u root -p < /tmp/dump.sql Dump Only Table structure # mysqldump -u root -p –single-transaction –databases ivr_cms –no-data > /tmp/dump.sql

How to take dump only table structure
# mysqldump -u root -p –all-databases –no-data > /tmp/dump.sql

The following SQL statement selects all customers with Country NOT containing the pattern “22”:
mysql> SELECT * FROM tbl_contentusage_mod_aircel WHERE Duration NOT LIKE ‘%22%’;

SET GLOBAL log_bin_trust_function_creators = 1;
SET GLOBAL group_concat_max_len=15000;

How to Create new table with old table with data without index. (and Rename)
mysql> create table New_Table_Name select * from OLD_Table_Name;

How to Create blank table with indexing. without data. (and Rename)
mysql> create table New_Table_Name like OLD_Table_Name;

 How to take table data in CSV file
mysql> select * into outfile ‘/tmp/abc1.csv’ fields terminated by ‘,’ from chandan_rename_table ;

How to repair myisam tables (for all databases)
# mysqlcheck –repair -u root -p –all-databases

How to repair myisam Single tables (for Single Table)
# mysqlcheck –repair -u root -p database Table_Name

How to check Login databasemysql> select database();

How to check Login Usermysql> select user();

How to check Size in Databases
mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024 “size in MB” FROM information_schema.TABLES GROUP BY table_schema;

How do I change the privileges for MySQL user that is already created
select user,host from mysql.user;

To show privileges:
show grants for ‘user’@’host’;

To change privileges, first revoke. Such as:
revoke all privileges on *.* from ‘user’@’host’;

Then grant the appropriate privileges as desired:
grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO ‘user’@’host’;

Finally, flush:
flush privileges;

—————————————————————————————————-

Shows all queries running for 5 seconds or more:
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’ AND TIME >= 5;

Checking, analyzing,optimizing and Repairing MySQL Tables through MySQLcheck

When a MySQL table got corrupted, We can use mysqlcheck command to repair it. Not only repair, mysqlcheck provides a excellent way to checks, repairs, optimizes and analyzes the tables. 

Lets see How it works (Ofcourse, Some examples included) 

1. Check a Specific Table in a Database

While being a system admin who maintains one of leading web hosting servers, I often came across Table crashes mostly with social engine, Joomla etc.. When a table says or feels like some tables are corrupted, I do run a mysqlcheck command to check that ones.

The following example checks “xyz” table in teksupport database.

# mysqlcheck -c teksupport xyz -u root –p    
Enter password:
teksupport.xyz    OK

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

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

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 teksupport -u root -p
Enter password:
teksupport.ABC_TS2                            OK
teksupport.ABC_TS1                            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:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    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 teksupport and techie database.

# mysqlcheck -c  -u root -p --databases teksupport techie
Enter password:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK 
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    OK 
..

4. Analyze Tables using Mysqlcheck

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

# mysqlcheck -a teksupport XYZ1 -u root -p
Enter password:
teksupport.employee   Table is already up to date

Internally mysqlcheck command uses “ANALYZE TABLE” command. When mysqlcheck is executing, the analyze command the table will be locked and available for other process only in the read mode.i.e. No changes can be made on the data.

5. Optimize Tables using Mysqlcheck

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

# mysqlcheck -o teksupport XYZ1 -u root -p
Enter password:
teksupport.XYZ1               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 teksupport database.

# mysqlcheck -r teksupport EMP1 -u root -p
Enter password:
teksupport.XYZ1          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 teksupport database.

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

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 teksupport EMP1
Enter password:
teksupport.EMP1   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 456, 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

Mysqlcheck: Table maintenance and repair procedures

The Mysqlcheck client can examine and repair the MyISAM table. It can also optimize and analyze tables.

Mysqlcheck function is similar to MYISAMCHK, but its work is different. The main difference is that when the MYSQLD server has to use Mysqlcheck at run time, Myisamchk applies to the server when it is not running. The advantage of using Mysqlcheck is that you do not need to stop the server to check or fix the table.

Mysqlcheck provides a convenient way for users to use SQL statements check table, REPAIR table, ANALYZE table, and optimize table. It determines which statement is used in the operation to be performed, and then sends the statement to the server to be executed.

There are 3 different ways to invoke Mysqlcheck:

shell> mysqlcheck[options] db_name [tables]

Shell> Mysqlcheck[options]---database DB1 [DB2 DB3 ...]

Shell> Mysqlcheck[options]--all--database

If you do not specify any tables or use the---database or--all--database option, check the entire database.

Compared with other clients, Mysqlcheck has a special feature. Renaming the binary allows you to change the default behavior of the checklist (--check). If you want a tool that can fix a table by default, simply copy Mysqlcheck to Mysqlrepair or use a symbolic link mysqlrepair link mysqlcheck. If you call Mysqlrepair, you can repair the table by command.

The following name can be used to change the default behavior of Mysqlcheck:

Mysqlrepair	The default option is--repair
Mysqlanalyze	The default option is--analyze
Mysqloptimize	The default option is--optimize

Mysqlcheck

The following options are supported:

· ---help,-?

Displays a help message and exits.

· --all--database,-a

Check all tables in all databases. As with the---database option, all databases are named on the command line.

· --all-in-1,-1

Instead of issuing a statement for each table, you execute a statement for each database in the named database for all the tables that are being processed.

· --analyze,-a

Analysis table.

· --auto-repair

If a checked table is corrupted, fix it automatically. All required repairs are done automatically after checking all tables.

· --character-sets-dir=path

The installation directory for the character set. See section 5.10.1, "Data and sorting with character sets."

· --check,-c

Check the table for errors.

· --check-only-changed,-c

Checks only those tables that have changed since the last check or have not been properly closed.

· --compress

Compresses all information sent between the client and the server, if both support compression.

· ---database,-b

Handles all the tables named in the database. With this option, all Word name parameters are treated as database names, not table names.

· ---debug[=debug_options],-# [debug_options]

Write debug Log. The debug_options string is usually ' d:t:o,file_name '.

· --default-character-set=charset

Use the Charsetas default character set. See section 5.10.1, "Data and sorting with character sets."

· --extended,-e

If you are using this option to check the table, make sure that they are 100% consistent, but will take a long time.

If you are using this option to fix the table, run the extended fix, not only for a very long time, but also for a lot of garbage rows!

· --fast,-f

Only check for tables that are not properly closed.

· --force,-f

Continue even if a SQL error occurs.

· --host=host_name,-h host_name

Connect to a MySQL server on a given host.

· --medium-check,-m

Perform a faster check than the--extended operation. Only 99.99% of the errors can be found, and in most cases this is enough.

· --optimize,-o

Tuning tables.

· --password[=password],-p[password]

The password to use when connecting to the server. If you use the Short option form (-p), there is no space between the option and the password. If you do not have a password value after the--password or P option on the command line, you are prompted to enter a password.

· --port=port_num,-p Port_num

The TCP/IP port number to use for the connection.

· --protocol={tcp | SOCKET | PIPE | MEMORY}

The connection agreement used.

· --quick,-q

If you are using this option in the checklist, it prevents scan rows to check for error link checks. This is the quickest way to check.

If you are using this option in the repair table, it tries to repair only the index tree. This is the quickest way to fix it.

· --repair,-r

Perform fixes that can fix most problems, except that the unique values are not fixed for a while.

· --silent,-s

Silent mode. Only error messages are printed.

· --socket=path,-s Path

The socket file to use for the connection.

· --tables

Overwrite---database or-b option. All parameters after the option are treated as table names.

· --user=user_name,-u user_name

The MySQL user name to use when connecting to the server.

· --verbose,-v

Verbose mode. Print information about program actions for each stage.

· --version,-v

Displays version information and exits.

Optimizing and repairing MySQL databases with mysqlcheck

 mysqlcheck which is a maintenance command line tool that allows you to check, analyze, repair, and optimize MySQL/MariaDB tables and databases.

Check one table in the database

The following command will check the table posts in the database blog:

$ mysqlcheck -c blog posts
blog.posts                         OK

If your database is protected by a password add  -u root -p at the end of the command:

$ mysqlcheck -c blog posts -u root -p
Enter password:
blog.posts                         OK

Analyze all tables in a database

The following command will check the table posts in the database blog:

$ mysqlcheck -a blog posts
blog.posts                         OK

If the MySQL/MariaDB server is running on a remote host, add  -h at the end of the command:

$ mysqlcheck -a blog posts -h remotehost.com
blog.posts                         OK

Optimize all tables in all database

$ mysqlcheck -o --all-databases
blog.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
mysql.time_zone_transition_type                    Table is already up to date

Table does not support optimize, doing recreate + analyze instead means that we’re doing OPTIMIZE on an InnoDB table that doesn’t support this option. When doing OPTIMIZE on an InnoDB table it creates an empty table, copies all rows from the existing table into to the new one, deletes the old one and renames the new table, and then runs ANALYZE on the table.
Table is already up to date means that the table is already up to date and there is no need to check it.

Repair multiple databases

The following command will repair all tables in both blog and blog2 databases:

$ mysqlcheck -r --databases blog blog2

If you see note : The storage engine for the table doesn't support repair it means that you are doing REPAIR on an InnoDB.

Optimize and repair all tables in all databases

The following command will check all tables in all databases and if some table is corrupted it will automatically fix it that table:

$ mysqlcheck --auto-repair -o --all-databases

Most used mysqlcheck arguments

-c, --check	Check table for errors.
-a, --analyze	Analyze given tables.
-o --optimize	Optimize the tables.
-r, --repair	Perform a repair that can fix almost anything except unique keys that are not unique.
--auto-repair	If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked.
-A, --all-databases	Check all the databases. This is the same as –databases with all databases selected.
-B, --databases	Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names.
--tables	Overrides the –databases or -B option such that all name arguments following the option are regarded as table names.
-g, --check-upgrade	Check tables for version-dependent changes. May be used with –auto-repair to correct tables requiring version-dependent updates.