MySQL常用命令

MySQL安装：

1. 解压安装mysql：basedir
2. 建立mysql数据目录：datadir
3. 创建mysql用户，目录授权
4. vi /etc/my.cnf
5. mysql DB初始化：mysql_install_db --basedir=... --datadir=...  --defaults-file=/etc/my.cnf --user=mysql
6. 启动mysql server： mysqld_safe --defaults-file=/etc/my.cnf  &
7. mysql client: mysql test <a.sql
8. cp support-files/mysql.server /etc/rc.d/init.d/mysql
9. service mysql start

http://blog.chinaunix.net/uid-13642598-id-3153537.html

http://blog.chinaunix.net/uid-13642598-id-3151743.html

https://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html
# Preconfiguration setup
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
# Beginning of source-build specific instructions
shell> tar zxvf mysql-VERSION.tar.gz
shell> cd mysql-VERSION
shell> mkdir bld
shell> cd bld
shell> cmake ..
shell> make
shell> make install
# End of source-build specific instructions
# Postinstallation setup
shell> cd /usr/local/mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> bin/mysql_install_db --user=mysql    # Before MySQL 5.7.6
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup              # MySQL 5.7.6 and up
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

MySQL client常用命令：

alter table t2 engine=innodb;
help show
show databases;
use <db>
show tables;
show table status like 't2' \G
show index from t2 \G
show create table t2;
show global variables like '%delay_key%';
show global variables like '%datadir%';
show global variables;
show session variables;
SHOW CHARACTER SET;
show variables like '%char%';
show engines;
set [global | session] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]

创建MySQL用户和DB：

mysql -uroot
use mysql
delete from user where user!='root' or host!='localhost';
update user set password=password('123123');
flush privileges;
mysql -uroot -p123123

create database zabbix character set utf8 collate utf8_bin;
grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by 'zabbix';
flush privileges;

备份/恢复：

mysqldump --single-transaction -uroot -p -A >all.sql
mysqldump --single-transaction -uroot -p test >test.sql
innobackupex --no-timestamp --default-file=/etc/my.cnf --user root --socket=/tmp/mysql.sock --password root123 /data/xtrabackup/allbak

http://blog.chinaunix.net/uid-13642598-id-3149307.html   MySQL系列培训视频汇总 

http://blog.csdn.net/wjtlht928/article/details/46641865  MyISAM与InnoDB两者之间区别与选择

==========================

如果要更改mysql编译配置:

make clean

rm -f CMakeCache.txt

重新编译即可

若遇到如下错误提示:

CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found.   Please set CMAKE_CXX_COMPILER to a valid compiler path or name.

解决:yum install gcc-c++ -y

===========================

mysql的data目录是在/usr/local/mysql/data，你可以移到/data下:

mv /usr/local/mysql/data /

vi /etc/my.cnf的[mysqld]中加入:

datadir = /data