Mysql常见命令集

物理机器授权指令序列：

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO root@xxx.xxx.xxxx.xxx IDENTIFIED BY "root";
FLUSH PRIVILEGES;

 

执行恢复脚本命令：

mysql -u root -p
-- source /xx/usbhdfiles/xxx.sql

 

查看索引，列信息，变量值以及设置变量值的命令序列：

SHOW INDEX FROM mydb.mytable;
SHOW COLUMNS FROM mydb.mytable;
SHOW VARIABLES LIKE '%conn%';
set global max_connections=200;

 

backup database:
mysqldump -u root -p metadata > /xxx/metadata.sql;

 

如果需要对当前的数据库应用程序没有影响，需要加如下的参数：

mysqldump --single-transaction --quick -u root -p webmetadata > /backup/webmetadata.sql;

无须密码的命令行如下：

mysqldump --single-transaction --quick -u root --password=xxxx webmetadata tb_store tb_cluster_store > /backup/metadata_0202.sql; 

 

查看当前所有连接的详细资料:
mysqladmin -u root -p -h 192.168.0.202 processlist

 

只查看当前连接数(Threads就是连接数.):
mysqladmin  -u root -p -h 192.168.0.203 status

 

MySQL死锁分析:
show innodb status;

 

----------------------------------------------------------------------------------------------------------------------------------------

MySQL BIN版本的安装和以前版本安装方法好像有些不同，不用进行congfiure和nake install。具体如下：
首先解压缩文件到/usr/lcoal/下并把名称改为MYSQL（mv mysql-6.0.0-alpha-linux-i686-glibc23 /usr/local/mysql)
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local/mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
shell> cp /disk2/mysql5.1/support-files/mysql.server /etc/rc.d/init.d/mysqld
shell> chown -R mysql:mysql /etc/rc.d/init.d/mysqld  (change basedir & datadir if needed)
shell> chkconfig --add mysqld (将mysql添加进自启动服务列表里)
shell> cp ./support-files/my-huge.cnf /etc/my.cnf
可以把/usr/local/mysql/bin下所有的命令链接到/usr/bin/下 (如：ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql)这样就不用经常输入全路径了。
ln -s /disk2/mysql5.1/bin/mysql /usr/bin/mysql
ln -s /disk2/mysql5.1/bin/mysqldump /usr/bin/mysqldump
ln -s /disk2/mysql5.1/bin/mysqladmin /usr/bin/mysqladmin
ln -s /disk2/mysql5.1/bin/mysqlimport /usr/bin/mysqlimport
ln -s /disk2/mysql5.1/bin/mysqlcheck /usr/bin/mysqlcheck

----------------------------------------------------------------------------------------------------------------------------------------