1、首先安装依赖包,避免在安装过程中出现问题
# yum install gcc gcc-c++ kernel-devel ncurses-devel bison cmake
# tar -zxvf boost_1_62_0.tar.gz
# cp -r /opt/soft/boost_1_62_0 /usr/local/boost
2、创建mysql用户及用户组
# groupadd mysql
# useradd -g mysql mysql
3、安装mysql
# tar zxvf mysql-5.7.16.tar.gz
# cd mysql-5.7.16
# cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.7.16 -DMYSQL_DATADIR=/data/mysqldata -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_BOOST=/usr/local/boost
# make
# make install
CMAKE参数说明:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql //默认安装目录
-DINSTALL_DATADIR=/usr/local/mysql/data //数据库存放目录
-DDEFAULT_CHARSET=utf8 //使用utf8字符
-DDEFAULT_COLLATION=utf8_general_ci //校验字符
-DEXTRA_CHARSETS=all //安装所有扩展字符集
-DENABLED_LOCAL_INFILE=1 //允许从本地导入数据
-DMYSQL_USER=mysql
-DMYSQL_TCP_PORT=3306
注意事项:
如安装失败重新编译时,需要清除旧的对象文件和缓存信息。
# make clean
# rm -f CMakeCache.txt
# rm -rf /etc/my.cnf
4、设置目录权限并初始化数据库
# chown -R mysql.mysql /opt/mysql-5.7.16
# cp /opt/mysql-5.7.16/support-files/my-default.cnf /etc/my.cnf
# cp /opt/mysql-5.7.16/support-files/mysql.server /etc/rc.d/init.d/mysqld(将mysql的启动服务添加到系统服务中)
# /opt/mysql-5.7.16/bin/mysqld --initialize --user=mysql --basedir=/opt/mysql-5.7.16 --datadir=/data/mysqldata(初始化数据库)
5、开启mysql服务
# service mysqld start
# chkconfig --add mysqld(加入开机启动服务)
# chkconfig mysqld on
7、登陆修改root密码
# /opt/mysql-5.7.16/bin/mysql -uroot -p(连接mysql,输入初始化时生成的随机密码)
mysql>alter user 'root'@'localhost' identified by 'abc123';
8、修改MySQL的root用户密码和授权用户管理某个数据库
grant all on db_book.* to jack@'%' identified by 'abc123';(新建用户账号和密码并授权用户来管理某个数据库)
flush privileges;
exit
\s (查看服务器状态)
#mysql的一些简单命令:
show databases;
use database;
show tables;
create database lufee;
create table users(userid varchar(8),username varchar(8));
insert into users values('001','zhen');
insert into users values('002','jack');
select * from users;
desc users;
备份:/opt/mysql/bin/mysqldump -uroot -p密码 数据库名 > data.bak
恢复:/opt/mysql/bin/mysql -uroot -p密码 数据库名 < data.bak
120726 11:57:22 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
skip-name-resolve是禁用dns解析,避免网络DNS解析服务引发访问MYSQL的错误,一般应当启用。
启用后,在mysql的授权表中就不能使用主机名了,只能使用IP ,出现此警告是由于mysql 表中已经存在有 localhost.localdomain 帐号信息。
我们把它删除就好了。
use mysql;
delete from user where HOST='localhost.localdomain';
Query OK, 2 rows affected (0.00 sec)
重启MYSQL ,发现警告已经没有啦。
数据库配置文件/etc/my.cnf 参考内容:
[mysqld]
port=3306
user = mysql
pid-file=/opt/mysql-5.6.22/mysql.pid
socket=/tmp/mysql.sock
basedir=/opt/mysql-5.6.22
datadir=/data/mysqldata
tmpdir=/data/mysqltmp
skip-external-locking
lower_case_table_names = 1
skip-name-resolve
key_buffer_size=512M
max_allowed_packet=32M
table_open_cache=2048
sort_buffer_size=2M
read_buffer_size=2M
myisam_sort_buffer_size=64M
thread_cache_size=64
query_cache_size=0
wait_timeout=3600
interactive_timeout=3600
max_connections=1500
max_connect_errors=99999999999
event-scheduler=on
log-bin-trust-function-creators=1
explicit_defaults_for_timestamp=true
##############binlog####################################
binlog_format=row
expire_logs_days=5
max_binlog_size=512M
##############errorlog##################################
log-error=/opt/mysql-5.6.22/log/mysql.err
#############Innodb######################################
innodb_buffer_pool_size=512M
innodb_buffer_pool_instances=32
innodb_log_file_size=512M
innodb_log_buffer_size=32M
innodb_log_files_in_group =4
innodb_lock_wait_timeout=50
innodb_file_io_threads=4
innodb_flush_method=O_DIRECT
innodb_print_all_deadlocks=1
innodb_file_format=Barracuda
################Slow log####################################
slow_query_log_file=/opt/mysql-5.6.22/log/slow-query.log
long_query_time=3
slow_query_log=1
####################general log##############################
general_log_file=/opt/mysql-5.6.22/log/general.log
#####################Replication############################
server-id=1
log_slave_updates
binlog-ignore-db=mysql,test
relay_log_recovery=1
skip_slave_start=true
slave_compressed_protocol=on
log-bin=mysql-bin
replicate-wild-ignore-table=mysql.%
innodb_strict_mode=1
#auto_increment_offset=2 ##the variables is not needed with this system
#auto_increment_increment=2 ##the variables is not needed with this system
#############################################################
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet=32M
[mysql]
#no-auto-rehash
#prompt=\U|d>
[myisamchk]
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout