List of Foreign Keys in Mysql

Following sql shows list of foreing keys in Mysql. You may require this information for foreign key manipulations, such as dropping a foreign key.

use INFORMATION_SCHEMA;

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
TABLE_NAME = '<table>';

You may require this information for foreign key manipulations, such as dropping a foreign key.

Resource: http://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column 

Connect MySQL From External Hosts

Edit the /etc/mysql/my.cnf file and change the bind-address directive to the server's IP address:

bind-address            = 127.0.0.1

After changing bind-address directive to server's IP address in /etc/mysql/my.cnf, MySQL daemon will need to be restarted using following command:

sudo service mysql restart

Mysql Start/Stop

Start Mysql:

mysqld

You can test whether your instance is up and running by issuing 'telnet localhost 3306'.


Shutdown Mysql:

mysqladmin -u [mysql_user] shutdown

JDBC ile Mysql'de Turkce Karakter Kullanımı

Mysql'de Türkçe karakterler sorunsuz kullanılabilmesi icin UTF8 karakter seti kullanılabilir. Mysql'de UTF8 kullanmak icin asagidaki adimlarin uygulanmasi gerekiyor:

1. Tablo olustururken standart karakter set olarak UTF8 seçilmeli.

CREATE TABLE 'video' (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


2. JDBC ile baglanirken, JDBC url'de karakter set olarak UTF-8 verilmeli.

jdbc:mysql://localhost/youtube?useUnicode=true&characterEncoding=UTF-8

Mysql Backup and Restore

Backup

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Following is an example of a database backup command:

mysqldump -u mysql_user -p mysql_pass clipbucket > clipbucket.sql

Restore 

The dump file retrieved from from mysqldump command can be restored using mysql command. Following is the general format of restore command: 

mysql -u [username] -p [password] [database_to_restore] < [backupfile] 


Following is an example of a database restore command:

mysql -u mysql_user -p mysql_pass clipbucket_2 < clipbucket.sql

Mysql Cheat Sheat

How To Export Query Results In Mysql

Use "INTO OUTFILE" directive in your queries to export data from mysql into a text file.

mysql> select * from videos order by artist INTO OUTFILE "c:\videos.csv"
Query OK, 3269 rows affected (0.05 sec)


Use "FIELDS TERMINATED BY" directive to determine delimiter between fields.

mysql> select * from videos order by artist INTO OUTFILE "c:\videos.csv" FIELDS TERMINATED BY "|";
Query OK, 3269 rows affected (0.06 sec)


If you do not specify absolute path in "INTO OUTFILE" directive, the file will be recorded in current database's path. For example, assume that mysql is installed in "D:\dev\mysql" and the current database is "video_db"; then the path of videos.csv would be "D:\dev\mysql\data\video_db".

How To Change MySql Root Password

In this example, I am going to change mysql root account's password from 1234 to 123456 using SQL. The queries are so self-explanatory. So, there is no need to describe them.

D:\dev\xampp\mysql\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a Source distribution

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

mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("123456") where User="root";
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

D:\dev\xampp\mysql\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a Source distribution

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

mysql>

Some Mysql-Specific Commands

Following is a list of some of mysql specific commands that I frequently use. You should be connected to mysql server through the command-line to execute the commands.

>show databases;
Display the list of databases hosted by the mysql server

>use db;
Selects the database to be working on.

>desc table;
Describes the metadata of the table;

>show tables;
Display tables of the selected database

>source test.sql;
execute an SQL script file