User Tools

Site Tools


Sidebar


guides:databases:mysql

Mysql Commands

create database

CREATE DATABASE mydb   DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

mysqldump

 mysqldump [options] db_name [tables]
 mysqldump [options] --databases db_name1 [db_name2 db_name3...]
 mysqldump [options] --all-databases

mysql loading sql files

 mysql -u USERNAME -p DATABASE < FILENAME.sql

Example:

 mysql -u root -p musicsite < /tmp/musicsite.sql

mysql import files

examples:

 mysql -u USERNAME -p
 mysql> LOAD DATA INFILE '/tmp/genres.csv' INTO TABLE musicsite_genres FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' (id, name);
 mysql> LOAD DATA LOCAL INFILE '/tmp/songs.csv' INTO TABLE musicsite_songs FIELDS TERMINATED BY '\t' ENCLOSED BY ''  (id, name);

grant root remote access

mysql -u root -p
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '[password]' WITH GRANT OPTION
GRANT ALL ON *.* TO 'root'@'203.38.191.178' IDENTIFIED BY '[password]' WITH GRANT OPTION;

optimizing your mysql db

Storage Engine: InnoDB

File: /etc/my.cnf

[mysqld]
# set this to around 80% of available memory, by default its only 8Mb which is too low.
innodb_buffer_pool_size = 2G

Storage Engine: MyISAM

File: /etc/my.cnf

[mysqld]
# set this to around 25% of available memory
# Note: (key_buffer_size + innodb_buffer_pool_size) < 80% of available memory
key_buffer_size=500M

myisam_sort_buffer_size=256M

read_buffer_size=4M
sort_buffer_size=4M
myisam_max_sort_file_size=20G

logging slow queries

add the following to your my.cnf to log slow queries

log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2

resetting password

1) First kill mysql process if its running

2) Then we run mysqld with –skip-grant-tables options

cd /usr/local/mysql
./mysqld_safe --skip-grant-tables --user=root

3) Run mysql console and execute following sql

mysql
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

replication

Reset replication slave to master

mysql > STOP SLAVE;
mysql > RESET SLAVE;
mysql > CHANGE MASTER to MASTER_HOST='<host>', MASTER_USER='<user>', MASTER_PASSWORD='<password>', MASTER_PORT=3306, MASTER_LOG_FILE='<file>', MASTER_LOG_POS=<position>;
mysql > START SLAVE;
guides/databases/mysql.txt · Last modified: 2013/01/08 15:03 by michaelc