self.code()

Projects:

Tags:

Welcome to my little code log.

Posts tagged mysql:

14Useful MySQL snippets

Mar posted 14 Mar 2009 and tagged

Duplicate a table:

mysql> CREATE TABLE duplicate_name SELECT * FROM original_name;

Pop out

1

mysql> CREATE TABLE duplicate_name SELECT * FROM original_name;

Set root password:

mysql> UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root'; mysql> FLUSH PRIVILEGES;

Pop out

1
2

mysql> UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

Dump MySQL database routines only:

mysqldump -u user_name -p --routines --no-data --no-create-db --no-create-info database_name > routines.sql

Pop out

$

mysqldump -u user_name -p --routines --no-data --no-create-db --no-create-info database_name > routines.sql

Limit rows in mysqldump:

mysqldump --where="true LIMIT 10000" -u user_name -p database_name table_name > table_name_10000.sql

Pop out

$

mysqldump --where="true LIMIT 10000" -u user_name -p database_name table_name > table_name_10000.sql

Change table’s character set to utf8:

mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

Pop out

1

mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

A simple find-and-replace:

mysql> UPDATE table_name SET column_name = REPLACE(column_name, 'find this', 'replace with this');

Pop out

1

mysql> UPDATE table_name SET column_name = REPLACE(column_name, 'find this', 'replace with this');

12MySQL: Authorize and select database with bash function

Mar posted 12 Mar 2009 and tagged , ,

Place in ~/.bash_login, ~/.bash_profile, or ~/.bashrc after changing lines 2 and 3:

my() { user="your_username"; pass="your_password"; if [ $# = 1 ] then mysql -u $user --password=$pass $1; else mysql -u $user --password=$pass --execute='show databases'; mysql -u $user --password=$pass; fi }

Pop out

1
2
3
4
5
6
7
8
9
10
11

my() {
  user="your_username";
  pass="your_password";
  if [ $# = 1 ]
  then
    mysql -u $user --password=$pass $1;
  else
    mysql -u $user --password=$pass --execute='show databases';
    mysql -u $user --password=$pass;
  fi
}

Before:

mysql -u [user] -p ([database])

Pop out

$

mysql -u [user] -p ([database])
Enter password:
mysql >

After:

my ([database])

Pop out

$

my ([database])
mysql >