MYSQL系列-03Linux平台下通过源码方式安装 mysql

MYSQL系列-03Linux平台下通过源码方式安装 mysql

操作系统环境配置此处略，大家可参考上一篇二进制安装方式有此步骤
make < mysql5.5 ,cmake > mysql5.5

--1 环境检查
mount /dev/cdrom /mnt
yum -y install lrzsz libaio  libaio-devel  bison  bison-devel zlib-devel  openssl openssl-devel  ncurses ncurses-devel  libcurl-devel boost  boost-devel lsof wget gcc gcc-c++ make cmake perl  kernel-headers kernel-devel pcre-devel
 libarchive-devel  --OL6中没有此包
dtrace*--需要网上下载
--2 安装过程
1）上传安装程序
2）创建用户，组，创建目录
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
--
mkdir -p /mysql/data/3306/data
mkdir -p /mysql/log/3306
3) 解压安装包程序
--检查原来的安装包
rpm -qa|grep mysql
rpm -qa|grep mysql/Maria*/maria*
--卸载
rpm -e mysql-devel-5.1.73-8.0.1.el6_8.x86_64
rpm -e mysql-5.1.73-8.0.1.el6_8.x86_64
4) 修改权限
chown -R mysql:mysql /mysql
5）配置环境变量
vi ~/.bash_profile
/mysql/app/mysql/bin

source ~/.bash_profile
6) 编译安装
安装cmake，对此版本号有很高的要求，不同系统版本，cmake版本号依赖不同。ol6-3.5.2
tar zxvf cmake-3.5.2.tar.gz
cd cmake-3.5.2
 ./bootstrap
gmake --编译
gmake install --安装
cmake --version --查看安装版本

cd /mysql/app/mysql-5.7.20
cmake . -DCMAKE_INSTALL_PREFIX=/mysql/app/mysql -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/mysql/data/3306/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DSYSCONFDIR=/mysql/data/3306 -DMYSQL_UNIX_ADDR=/mysql/data/3306/mysql.sock -DMYSQL_TCP_PORT=3306 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/mysql/app/mysql-5.7.20/boost/boost_1_59_0
make
make install
7）准备配置文件 my.cnf及初始化 mysql
vi  /mysql/data/3306/my.cnf
[client]
port=3306
#socket	= /data/mysql/mysql.sock

[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8


[mysqld]
########basic settings########
server-id=3306
port=3306
#user = mysql
bind_address = 0.0.0.0
basedir="E:\\MySQL\\MySQL57"
datadir=E:\\MySQL\\data\\Data
#tmpdir="E:\\MySQL\\tmp"
#socket	= /data/mysql/mysql.sock
#pid-file = mysqldb.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
federated
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp=1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general-log=0
general_log_file=kingdb-general.err
slow-query-log=1
slow_query_log_file=kingdb-query.err
long_query_time=10
log-error=kingdb-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row 
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors


########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M 

innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2 
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
#生产环境建议2个1G，最后再弄个自动扩展5G，根据自己的事物对定。
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
初始化数据库
/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data

8）配置启停脚本与测试
/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir='/mysql/data/3306/data' &

通过初始密码登录MySQL，并修改密码
[root@2snqobz5vv1d7s bin]# vi /mysql/log/3306/kingdb-error.log
mysql --defaults-file=/mysql/data/3306/my.cnf -uroot -p
设置root账户密码为root（也可以修改成你要的密码）
mysql>set password=password("root");
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
grant all privileges on *.* to 'root'@'localhost' identified by 'root' with grant option;
flush privileges; --授权生效


 11)设置开机自启动服务控制脚本

-复制启动脚本到资源目录
[root@localhost mysql]#cd /mysql/app/mysql/support-files/
cp mysql.server mysql
[root@mysql support-files]# vi mysql
      46 basedir=/mysql/app/mysql
     47 datadir=/mysql/data/3306/data
     63 mysqld_pid_file_path=/mysql/data/3306/mysql.pid
    266  $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
------根据需要看添加mysql服务，还是Mysqld服务器
#cp mysql /etc/rc.d/init.d/mysqld
-增加mysqld服务控制脚本执行权限
[root@localhost init.d]#
chmod u+x /etc/rc.d/init.d/mysqld
或者
# cp /etc/rc.d/init.d/mysqld /etc/init.d/mysql
chmod u+x /etc/init.d/mysql
或者
-将mysqld服务加入到系统服务
[root@localhost init.d]#
chkconfig --add mysql
-检查mysqld服务是否已经生效
[root@localhost init.d]#
chkconfig --list mysql
命令为:service mysql start和service mysql stop

注意关闭MYSQL服务不能通过KILL pid的方式实现，需要运行如下命令

 /mysql/app/mysql/bin/mysqladmin --defaults-file=/mysql/data/3306/my.cnf -u root -p shutdown

8) 后期配置（密码，启动远程访问）
-制作启动脚本
[root@mysql 3306]# vi mysql.start
/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --user=mysql &
[root@mysql 3306]# chmod u+x mysql.start
如果没有在前面增加开机启动服务，利用此脚本也可以实现开机自动启动
vi /etc/rc.local --添加配置行
/mysql/data/3306/mysql.start

-关闭mysql
[root@mysql 3306]# mysqladmin -uroot -p shutdown -S/mysql/data/3306/mysql.sock
或者
mysqladmin --defaults-file=/mysql/data/3306/my.cnf -u root -p shutdown
-密码过期问题解决 my.cnf添加如下配置
[mysqld]
skip-grant-tables
-连接错误问题
mysql -uroot -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

ln -s /mysql/data/3306/mysql.sock /tmp
或者
mysql -uroot -p -S/mysql/data/3306/mysql.sock 

-数据库预读问题
mysql -uroot -p -A

-查看root属性
mysql> select * from mysql.user where user='root'\G
-改密码，通过初始密码登录MySQL，并修改密码
[root@2snqobz5vv1d7s bin]# vi /mysql/log/3306/kingdb-error.log
mysql --defaults-file=/mysql/data/3306/my.cnf -uroot -p
设置root账户密码为root（也可以修改成你要的密码）
mysql>set password=password("root");
flush privileges; --授权生效
-远程登录问题
use mysql
select host,user from user where user='root';
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
grant all privileges on *.* to 'root'@'localhost' identified by 'root' with grant option;
flush privileges; 
-启停验证，日志查看
service mysqld start
service mysqld stop
或者
service mysql start
service mysql stop
或者
/mysql/data/3306/mysql.start
mysqladmin -uroot -p shutdown -S /mysql/data/3306/mysql.sock

tail -f /mysql/log/3306/*err*.out

基础SQL语句
--1.创建一个数据库
create database king;
create database king2 default charset utf8 collate utf8_general_ci;
--2.创建用户并授权 
create user 'king'@'%' identified by 'king';
grant all privileges on king.* to 'king'@'%' identified by 'king';
grant all privileges on king.* to 'king'@'localhost' identified by 'king';
--grant all privileges on king.* to 'king'@'%' identified by 'king' with grant option;
flush privileges;

--检查用户
select host,user from mysql.`user`;
--4.创建表和数据插入
use king2
create table dept (
deptno int auto_increment primary key,
dname varchar(15),
loc varchar(50)
) engine = innodb;

insert into dept values (1,'it','bj');
insert into dept values (2,'cw','sh');
insert into dept values (3,'hr','sz');
commit;
select * from dept;