3/26/2010

MySQL Commands.

This is for my notes.

Update Syntax.

 mysql> UPDATE table_name SET field1 = value1, field2 = value2 WHERE Clause;
 mysql>  use mysql;
 mysql> UPDATE user SET Host='192.168.1.%' WHERE User = 'user1';


Delete user.
Drop command is better than delete command.
 mysql> drop user 'user'@'%';

 mysql> delete from user where User = '' and Host = 'localhost';

Set password.
 mysql> set password for root@localhost = password('abcde');

Create DB and grant/create new user with password.
 create database someDB;
 GRANT ALL PRIVILEGES ON someDB.* TO username@"%" IDENTIFIED BY 'password';

Select: Display results one by one.
select * from some_table \G;

Pager option
Shell>mysql --pager='less -S' -uroot -p

Output as CSV
select * from some_table into outfile "./output.csv" fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

Purge binary logs
On slave machine, check the current log position
 mysql> show slave status\G;
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
 Master_Host: masterdb 
 Master_User: user 
 Master_Port: 3306 
 Connect_Retry: 60 
 Master_Log_File: mysqld-bin.000037

 Read_Master_Log_Pos: 170665205
 Relay_Log_File: mysqld-relay-bin.000063
 Relay_Log_Pos: 170665343
 Relay_Master_Log_File: mysqld-bin.000037

On master machine, check the current status.

 mysql> show master status\G;
*************************** 1. row *************************** 
File: mysqld-bin.000037
Position: 170680957
Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

mysql> show master logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| mysqld-bin.000035 | 1073743657 | 
| mysqld-bin.000036 | 1073745063 | 
| mysqld-bin.000037 |  171424254 | 
+-------------------+------------+
3 rows in set (0.00 sec)


On master, to delete old binary logs which is prior to mysqld-bin.000037
mysql> purge master logs to mysqld-bin.000037



















No comments: