MYSQL Notes

Download: http://dev.mysql.com/downloads/
Install Size (5.1.22 tgz): 270M

ln -s /usr/local/mysql/data /var/lib/mysql
vi /etc/my.cnf
---
[client]
host=localhost
user=root
password=p4ssword
socket=/var/lib/mysql/mysql.sock
promt = (\d)> 
#lower_case_table_names

[mysqld]
# skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
user=mysql
old_passwords=1

# changes made for master slave replication (master=1, slave=2)
server-id = 1

# log-bin is only needed on master
expire-logs-days = 7 
max_binlog_size = 100M
log-bin = /var/lib/mysql/mysql-bin
#binlog-do-db=cop
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
log-output=FILE
log-queries-not-using-indexes
log-slow-queries = /var/lib/mysql/mysqld_slow-queries.log
long-query-time=2.5

# this section is needed only on slave
#relay-log = /var/lib/mysql/relay-bin
#relay-log-index = /var/lib/mysql/relay-bin.index
#relay-log-info-file = /var/lib/mysql/relay-log.info
#master-info-file = /var/lib/mysql/master.info

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
---

mysql> show create table City\G
mysql> desc City;
mysqlshow world City Name
mysql> show warnings;
mysql> flush privileges;
mysql> select version();
mysql> help contents;
mysql> help show collation;
tee mysession.log
notee

mysql> status
mysql> show status;
mysql> show variables like = 'datadir';
mysql> show status like = 'Threads_connected';
mysql> set global sort_buffer_size=512000;
mysql> show status like = 'Aborted%';
mysql> show variables like '%buffer%';
mysql> show variables like '%cache%'
mysql> show variables like '%key%'
mysql> show variables like '%max%'

mysql> show global status like '%qcache%';
mysql> show status like '%tmp%tab%';

mysql> CREATE VIEW CountryCapitals AS SELECT CountryCode, Country.Name AS Country, City.Name AS Capital, City.Id AS CapitalID FROM Country JOIN City ON Capital = Id;
mysql> select * from CountryCapitals where Country like 'Sw%';
mysql> update CountryCapitals set CountryCode = 'CH' where Country like 'Switzerland' ;

mysql> show status like '%cost%';
mysql> select Country.Name from Country join CountryLanguage ON Code=CountryCode where Language = 'Russian';
mysql> explain select Country.Name from Country join CountryLanguage ON Code=CountryCode where Language = 'Russian'\G
mysql> alter table CountryLanguage add index (Language);
mysql> explain select Country.Name from Country join CountryLanguage ON Code=CountryCode where Language = 'Russian'\G
mysql> select @A:=7;
mysql> select count(distinct left(name,@A)) as 'distinct prefix values', count(*) - count(distinct left(name, @A)) as 'duplicate prefix values' from City;

mysql> show databases;
mysql> create database world;
mysql> use world;
mysql> SOURCE /tmp/world.sql;

mysql> select @@storage_engine; (information_schema)>select table_name, engine from tables where table_schema like 'world';
mysql> create table City_MEM engine = MEMORY SELECT * from City;
mysql> alter table City engine = myisam;
mysql> show table status;

mysql> create table city_temp like City;
mysql> insert into city_temp select * from City;
mysql> delete from city_temp where id between 1001 and 2000;
ls -l /var/lib/mysql/world
mysql> optimize table city_temp;
ls -l /var/lib/mysql/world

vi /var/lib/mysql/world/city_temp.MYI
mysql> check table world.city_temp extended; #error
mysql> repair table world.city_temp extended; #no way
mysql> repair table world.city_temp use_frm; #ok
mysql> check table world.city_temp extended; #ok

mysql> select * into outfile '/tmp/city.txt' from City;
od -c /tmp/city.txt | less
mysql> select * into outfile '/tmp/city.csv' fields terminated by ',' optionally enclosed by '"' from City;
mysql> create table city_import like City;
mysql> load data infile '/tmp/city.csv' into table city_import fields terminated by ',' optionally enclosed by '"';
mysql> load data infile '/tmp/city.csv' into table city_import fields terminated by ',' optionally enclosed by '"' ignore 5 lines;
mysql> load data infile '/tmp/city.csv' into table city_import fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

mysql> select * into outfile '/tmp/city.txt' from City;
mysql> CREATE TABLE `City_short` (`Name` char(35) NOT NULL DEFAULT '',`Country` char(52) NOT NULL DEFAULT '',`kPopulation` int(11) NOT NULL DEFAULT '0');
mysql> load data infile '/tmp/city.txt' into table City_short (@ID, Name, @CountryCode, @District, @Population) set Country = (select Name from Country where Code = @CountryCode), kPopulation = @Population/1000;

mysqladmin -uroot -p password p4ssw0rd
mysql> grant all on *.* to 'root'@'192.168.1.0/255.255.255.0' identified by 'p4ssword' with grant option;
mysql> grant all on *.* to 'root'@'192.168.1.%' identified by 'p4ssword' with grant option;
mysql> select user,host,password from mysql.user;
mysql> select user,host,password from mysql.user where password = '';
mysql> show grants for 'root'@'localhost';
mysql> delete from mysql.user where password = '';
mysql> flush prifileges;
mysql> drop user 'superuser'@'localhost';
mysql> create user 'superuser'@'localhost' identified by 'secret';
mysql> grant all privileges on *.* to 'sueruser'@'localhost' with grant option;
mysql> grant all privileges on *.* to  'chris'@'localhost' identified by 'secret' with grant option;


mysql> show collations;
mysql> show character set;
cat ./db_name/db.opt #show dp cahrset

mysql> show global status like 'Tab%lock%';
mysql> CREATE TABLE fixed(n DECIMAL(3,1));
mysql> INSERT INTO fixed values(-23.4);
mysql> select name, population from Country where name like '%witzerlan%';
mysql> select sleep(11);
mysql> prompt \u@\h [\d]>
mysql> select @a:=7;
mysql> select @a;
mysql> set sql_mode='ORACLE';
mysql> set sql_mode='';
mysql> select * from u <TAB,TAB>
mysql> update City set Population=Population*2;
mysql_upgrade --verbose #repair tables, check status of data, upgrade for new version

mysql> show variables like '%dir';
mysql> show variables like '%port%' ;
mysql> show variables like 'log_output';

mysql> use mysql;
mysql> describe time_zone;
mysql> show tables like 'time%zone%' ;
mysql> select * from time_zone;
mysql> select * from time_zone_name ;
mysql> select @@global.time_zone, @@session.time_zone;
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql # load time zones from os into mysql db

mysql> GRANT ALL ON world.* TO worlduser@localhost IDENTIFIED BY 'passw0rd';
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR 'root'@'localhost'

mysql> flush logs; #rotate logfiles
mysql> show binary logs;
mysqlbinlog /var/lib/mysql/mysql-bin.000001 #show content of binlog
mysql> purge binary logs to 'binlog.000004'; #delete logs before 

mysqldump world -uroot --master-data --single-transaction --flush-logs > world-$(date '+%Y%m%d-%H%M').sql
mysql> drop table City;
mysql> create table City select *  from Country;
mysql> show tables;
mysql> show master status;
mysql> show engine innodb status\G
mysql world < world-20081126-1342.sql
mysqlbinlog --disable-log-bin --start-position=106 --stop-position=185 mysql-bin.000012 | mysql world