Archive

Posts Tagged ‘mysql statements’

mysql most frequently used sql statements:login,set password,create-import-dump database,add user,repair table,etc.

October 13th, 2010 Comments off

Log in:
mysql -h localhost -u root -pyourpassword
set password:
set password for 'root'@'localhost' = password('123456');
create database:
CREATE DATABASE `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

add mysql account
CREATE USER 'user'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;
dump database
mysqldump -u root -pyourpassword --no-data --default-character-set=utf8 test>aaa.sql #--no-data,just the table definition without real lines
import
mysql -h localhost -u root -pyourpassword dbname</root/db.sql
repair table
repair table tablename;
#in detail:http://dev.mysql.com/doc/refman/5.1/en/table-maintenance-sql.html

Database Administration Statements
set,show,account management
#in detail:http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-server-administration.html

All sql statements
in detail:http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html

view amounts of active mysql connections
./mysqladmin -uroot -p -hlocalhost processlist #Or use 'show processlist' in mysql>;
./mysqladmin -uroot -p -hlocalhost status #Threads is the amount of connections
executives under mysql/bin
In detail:http://dev.mysql.com/doc/refman/5.1/en/programs.html
innochecksum                mysql_find_rows
msql2mysql                  mysql_fix_extensions
myisamchk                   mysql_fix_privilege_tables
myisam_ftdump               mysqlhotcopy
myisamlog                   mysqlimport
myisampack                  mysql_install_db
my_print_defaults           mysql_secure_installation
mysql                       mysql_setpermission
mysqlaccess                 mysqlshow
mysqladmin                  mysqlslap
mysqlbinlog                 mysqltest
mysqlbug                    mysql_tzinfo_to_sql
mysqlcheck                  mysql_upgrade
mysql_client_test           mysql_waitpid
mysql_config                mysql_zap
mysql_convert_table_format  perror
mysqld_multi                replace
mysqld_safe                 resolveip
mysqldump                   resolve_stack_dump
mysqldumpslow