MYSQL Notes
Jump to navigation
Jump to search
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