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