Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Home
Subscribe
Ask a Question
Advertise
About Us

Reset MySQL root password

 MySQL  No Responses »
Feb 062010
 

Reset MySQL root password

Answer:

If you have forgotten the MySQL root password, follow the steps below to reset the root password (for Ubuntu/Debian).

1. Shutdown MySQL

# /etc/init.d/mysql stop

2. Startup MySQL using command without grant tables, take the process to run in background

# /usr/bin/mysqld_safe --skip-grant-tables &

3. Login into the MySQL database

# mysql --user=root mysql

4. Reset password


mysql>update user set Password=PASSWORD('newpassword') 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.02 sec)

5. Shutdown MySQL

Bring the MySQL server process to foreground now and kill it by Ctrl-C

# fg
[Ctrl-C]

6. Start MySQL using normal way

# /etc/init.d/mysql start

That's all.

Show all MySQL supported character sets

 MySQL  No Responses »
Feb 062010
 

Show all MySQL supported character sets

Answer:

Execute the following command in the MySQL's shell.

mysql> SHOW CHARACTER SET

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

Prepare statement in MySQL

 MySQL  No Responses »
Jan 302010
 

Prepare statement in MySQL

Answer:

MySQL 5.1 provides support for server-side prepared statements, allow you to code more efficient database driven program.

Example:

mysql> PREPARE stmt_password FROM 'SELECT password(?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @a = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 |
+-------------------------------------------+
1 row in set (0.00 sec)

As you can see above, once you have prepared the statement in server, you can reuse the statement by injecting a new value. It is faster since MySQL only need to one time SQL statement parsing, rather than parse your SQL statement every time and execute.

Create temporary table in MySQL

 MySQL  No Responses »
Jan 272010
 

Create temporary table in MySQL

Answer:

Temporary table is very useful when you need some tables for temporary storage, and they will be automatically removed when you disconnect from the MySQL server.

Steps:

1. Create a temporary table

mysql> CREATE TEMPORARY TABLE test (id int);

2. Insert some values

mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (2);
Query OK, 1 row affected (0.00 sec)

3. Query the table

mysql> SELECT * FROM test

+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

4. Close the connection and the table will be automatically removed.

 Older Entries  Newer Entries