MySql

Useful tools for MySql

PhpMyAdmin

http://www.phpmyadmin

MySqlWorkbench

MySqlWorkbench

Other Mysql

Snippets

update uploads set itunes = 'Y' where itunes = 'A'; update uploads set flash = 'Y' where flash = 'S'; update uploads set process_status = 'P', transfer_status = 'P';*

update transfers set process_status = 'P' where process_status in ('A', 'S'); update transfers set transfer_status = 'P';

select media, reason, target_directory, filename,id, process_status, transfer_status, target_server, filesize, duration, matched_id, reason from transfers;

select id, itunes, flash, process_status, transfer_status, parent_id, media, website from uploads;

update uploads set itunes = 'Y' where itunes = 'A';

My.cnf

  1. Sample my.cnf can be found in /usr/local/mysql/support-files/ directory
  2. file is called .my.cnf

Create file called .my.cnf in ~
Enter:

[client]
user = root
password = password
[mysql]
prompt='mysql [\h] {\u} (\d) > '

Standard command line

mysql -h mysql-server.ucl.ac.uk -u ccspolm -ppassword OR if use .my.cnf above then

mysql -h mysql-server.ucl.ac.uk -u ccspolm olm_dev

show create table (including Engine)

show create table itunes;

Mysqldump

# mysqldump -h mysql-d.ucl.ac.uk -u ccspolm --opt olm_dev > ~/tmp/olm_dev.sql

Specifying the —opt argument when backing up our database should theoretically give us the fastest possible dump for reading back into MySQL

No data

# mysqldump -h mysql-d.ucl.ac.uk -u ccspolm --opt olm_dev > ~/tmp/olm_dev.sql

Unicode and MySql:

create database olm_dev CHARACTER SET utf8;

Innodb versus MYISAM

All *Innodb* databases are in a single file. N.B. *myisam* is the default.

Useful

Use:

du -ks * | sort -n

to view by size.
mysql -u root -p

When in mysql use:
show master status

however on the slave:
show slave status\G

To stop:
slave stop;

To see what is happening:
show processlist;

To extract a given database.table
$ scandump <dbname> <tablename> < <dumpfile> >  <sqlfile>

when back in mysql
mysql> source <sqlfile>

1-4-09 setting/changing MySql root password

To stop mysql:

sudo /etc/init.d/mysql start

Put the following into a text file (say reset.txt:
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Then restart MySql with the following:
sudo /etc/init.d/mysql start --init-file=reset.txt

Useful article on resetting root password

MySql Version

select version();

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License