MySQL commands

==========================
Backup database using dump
==========================
1.- Backup
mysqldump -u root xcms > new-xcms.sql

2.- Restore
(Make sure we have created the corresponding database)
mysql -u root -proot xcms < new-xcms.sql

3.-Rename DB
---------------
mysql -e "CREATE DATABASE \`new_database\`;"

for table in `mysql -B -N -e "SHOW TABLES;" old_database`
do
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
done
mysql -e "DROP DATABASE \`old_database\`;"
----------------



4.-Create DB
create database old_xcms;

5.- Backup triggers and stored procedures
mysqldump -u root --all-databases --triggers --routines -d -t > dbMasterProc.sql

6.- Create user and grant all privileges
mysql> create user 'root'@'172.16.%';
mysql> grant all privileges on *.* to 'root'@'172.16.%';
mysql> show grants;
+--------------------------------------------------+
| Grants for root@172.16.%                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.%' |
+--------------------------------------------------+
1 row in set (0.00 sec)

7.- Skip execution of one row in replication servers:
mysql> set global sql_slave_skip_counter = 1;

############
Show columns
############
mysql> show columns from tbl_gamechat;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| GCHT_iID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| GCHT_TLST_iID  | int(10) unsigned     | NO   | MUL | 0       |                |
| GCHT_GAM_iID   | int(10) unsigned     | NO   | MUL | 0       |                |
| GCHT_PLY_iID   | int(10) unsigned     | NO   | MUL | 0       |                |
| GCHT_cText     | varchar(255)         | NO   |     |         |                |
| GCHT_dDate     | timestamp            | YES  | MUL | NULL    |                |
| GCHT_iChatType | smallint(5) unsigned | NO   |     | 0       |                |
+----------------+----------------------+------+-----+---------+----------------+

##########
See rows
##########

mysql> select * from tbl_gamechat where GCHT_iID='24875';
+----------+---------------+--------------+--------------+------------+---------------------+----------------+
| GCHT_iID | GCHT_TLST_iID | GCHT_GAM_iID | GCHT_PLY_iID | GCHT_cText | GCHT_dDate          | GCHT_iChatType |
+----------+---------------+--------------+--------------+------------+---------------------+----------------+
|    24875 |          1148 |       756640 |      1029050 |            | 2013-02-19 12:31:03 |              0 |
+----------+---------------+--------------+--------------+------------+---------------------+----------------+
1 row in set (0.00 sec)

##########
Delete row
##########

mysql> delete  from tbl_gamechat where GCHT_iID='24875';