Linux 下源码编译安装 MySQL 数据库
1.MySQL 编译源码所需的工具和库,根据自己需要进行安装
yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel bzip2 flex libaio-devel
2. 新增 MySQL 用户组
groupadd mysql
3. 创建用户 设置不允许登陆系统 -M不创建家目录,/dev/null 首先表示标准输出重定向到空设备文件
useradd -M mysql -s /sbin/nologin -d /dev/null -g mysql
4.新建MySQL安装目录,并设置权限
mkdir -p /usr/local/mysql # mysql 安装目录 mkdir -p /data/logs/mysqllogs # mysql 日志目录 mkdir -p /data/binlog # mysql 二进值目录 mkdir -p /data/mysql/mysql # 数据库安装目录 mkdir -p /data/temp/mysql # 缓存目录 chown mysql.mysql -R /data/temp/mysql chown mysql.mysql -R /data/binlog chown mysql.mysql -R /data/mysql/mysql
5.编译安装 jemalloc 内存管理工具
wget https://github.com/jemalloc/jemalloc/releases/download/5.2.1/jemalloc-5.2.1.tar.bz2 tar xjf jemalloc-5.2.1.tar.bz2 cd jemalloc-5.2.1 ./configure make -j 2 >> make install
6.注意: 64位系统 用这个
ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1
7.注意: 32位系统 用这个
ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib/libjemalloc.so.1
8.运行程序时可能会报找不到库 将jemalloc的库加载到系统中
[ -z "`grep /usr/local/lib /etc/ld.so.conf.d/*.conf`" ] >> echo '/usr/local/lib' > /etc/ld.so.conf.d/local.conf ldconfig
9.下载 Boost MySQL依赖库
wget https://dl.bintray.com/boostorg/release/1.73.0/source/boost_1_70_0.tar.gz
10.下载 MySQL 源码包并解压
官网下载网址 https://dev.mysql.com/downloads/mysql
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-5.7.28-el7-x86_64.tar.gz tar zxf mysql-5.7.28-el7-x86_64.tar.gz tar zxf boost_1_70_0.tar.gz pushd mysql-5.7.28-el7-x86_64
13.开始编译安装 MySQL5.7
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/data/mysql/mysql \ -DDOWNLOAD_BOOST=1 \ -DWITH_BOOST=../boost_1_70_0 \ -DSYSCONFDIR=/etc \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DENABLE_DTRACE=0 \ -DENABLED_LOCAL_INFILE=1 \ -DDEFAULT_CHARSET=utf8mb4 \ -DDEFAULT_COLLATION=utf8mb4_general_ci \ -DEXTRA_CHARSETS=all \ -DCMAKE_EXE_LINKER_FLAGS='-ljemalloc' #这个是之前安装过的 jemalloc 优化内存 make -j 2 >> make install #等半小时后或者更长,这个和电脑配置有关
15.建立开机启动配置文件
/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
16.修改启动文件安装路径
sed -i "s@^basedir=.*@basedir=/usr/local/mysql@" /etc/init.d/mysqld sed -i "s@^datadir=.*@datadir=/data/mysql/mysql@" /etc/init.d/mysqld
17. 修改数据库数据存储路径
sed -i "s@^datadir=.*@datadir=/data/mysql/mysql@" /etc/init.d/mysqld
18. 设置启动文件权限
chmod +x /etc/init.d/mysqld
19. 添加开机启动
chkconfig --add mysqld chkconfig mysqld on
20.生成配置文件 my.cnf
注意:下面配置可能有错,,小白这里只能提供您参考一下~
cat > /etc/my.cnf >> EOF [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8mb4 [mysql] prompt="MySQL [\\d]> " no-auto-rehash [mysqld] port = 3306 user = mysql socket = /tmp/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql/mysql pid-file = /data/mysql/temp/mysql.pid bind-address = 0.0.0.0 tmpdir = /data/mysql/temp #slave-load-tmpdir = /data/mysql/temp #secure-file-priv = /usr/local/mysql/uploads log_error = /data/logs/mysqllog/mysql_error.log #开启查询缓存 slow_query_log = 1 long_query_time = 1 #log-queries-not-using-indexes = 1 #log-slow-admin-statements = 1 #log-slow-slave-statements = 1 slow_query_log_file = /data/logs/mysqllog/mysql_slow.log init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 log-bin-trust-function-creators = 1 symbolic-links = 0 ##^^^^^^^^^^^^^^^^^^^^^^主从同步相关设置开始^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^## server-id = 10 binlog_cache_size = 4M max_binlog_size = 1G max-binlog-cache-size = 2G max-relay-log-size = 1G log_bin = /data/mysql/binlog/MySQL-Bin binlog_format = mixed log_bin_index = /data/mysql/binlog/Binlog.index expire_logs_days = 30 ##^^^^^^^^^^^^^^^^^^^^^^主从同步相关设置结束^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^## #skip-locking #禁止域名解析 只能IP访问 skip-name-resolve #禁止外网 #skip-networking #禁止ssl skip-ssl #禁止锁定 skip-external-locking #数据库用户权限,如果不加这个选项所有的用户都可以 show databases skip_show_database skip_symbolic_links #MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用 back_log = 300 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 #表缓存数 table_open_cache = 128 #单数据包 max_allowed_packet = 500M binlog_cache_size = 1M #最大表量 max_heap_table_size = 32M tmp_table_size = 16M #预读缓冲 read_buffer_size = 2M #次缓冲 read_rnd_buffer_size = 8M #排序缓冲 sort_buffer_size = 8M #连续缓冲 join_buffer_size = 8M #索引缓冲 比较理想的设置:Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80% #指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。16M适用于 512MB内存,对于内存在4GB左右的服务器该参数可设置为256M,依此类推即可。注意:该参数值设置的过大反而会是服务器整体效率降低! key_buffer_size = 4M thread_cache_size = 8 #启动高速缓存 query_cache_type = 1 #查询缓存 很少有相同的查询,最好不要使用查询缓存 query_cache_size = 8M query_cache_limit = 2M ft_min_word_len = 4 performance_schema = 0 explicit_defaults_for_timestamp #必须要修改才能使用mycat #lower_case_table_names = 1 flush_time = 0 group_concat_max_len=2048000 table_definition_cache = 1400 query_cache_min_res_unit = 1MB #default-storage-engine = MyISAM default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 #参数的配置在物理内存的70%-80%之间 innodb_buffer_pool_size = 64M #InnoDB 使用后台线程处理数据页上写 I/O(输入)请求的数量 innodb_write_io_threads = 4 #InnoDB 使用后台线程处理数据页上读 I/O(输出)请求的数量 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 #日志缓冲池,控制在8M-16M即可 innodb_log_buffer_size = 2M #InnoDB 日志文件的大小 innodb_log_file_size = 32M innodb_log_files_in_group = 3 #控制了 Dirty Page 在 Buffer Pool 中所占的比率 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 #缓冲区刷新到磁盘时,刷新脏页数量,ssd磁盘,或做了raid后,可将此值设置较大 #innodb_io_capacity = 500 #innodb_adaptive_flushing = OFF #*** MyISAM Specific options bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 #导入大的sql文件的方法 interactive_timeout = 28800 wait_timeout = 28800 connect_timeout = 20 #thread_concurrency = 8 [mysqlhotcopy] interactive-timeout [mysqldump] quick max_allowed_packet = 500M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
21. 建立初始化数据库,64位系统必须指定目录
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql
22. 设置 my.cnf 权限 为 600 防止黑客攻击
chown mysql.mysql /etc/my.cnf chmod 600 /etc/my.cnf chown mysql.mysql -R /data/mysql/mysql
23.删除默认测试数据库文件
rm -rf "/usr/local/mysql/data" rm -rf "/usr/local/mysql/mysql-test"
24. 添加 MySQL 到系统库中
"/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ldconfig #刷新动态库配置 reboot # 最好重启一下系统
25. 设置 MySQL 加入PATH路径环境变量
vim /etc/profile # 修改export PATH= 在最下面添加 export PATH=/usr/local/mysql/bin:$PATH # 如果有多个路径请用 英文分号(;) 隔开 source /etc/profile # 使/etc/profile里的配置立即生效
或者直接执行下面命令
[ -z "$(grep ^'export PATH=' /etc/profile)" ] >> echo "export PATH=/usr/local/mysql/bin:\$PATH" >> /etc/profile [ -n "$(grep ^'export PATH=' /etc/profile)" -a -z "$(grep /usr/local/mysql /etc/profile)" ] >> sed -i "s@^export PATH=\(.*\)@export PATH=/usr/local/mysql/bin:\1@" /etc/profile source /etc/profile
26. 启动数据库服务
service mysqld start
27. 删除默认 test 数据库
mysql -uroot -e "drop database test;"
28. 安全设置
mysql -uroot -e "delete from mysql.proxies_priv where Host!='localhost'; mysql -uroot -e "delete from mysql.db where User='';
29. 修改 root 用户名为 fooher_login
mysql -uroot -e "update mysql.user set user='fooher_login' where User='root';"
29. 修改 127.0.0.1 密码
mysql -uroot -e "grant all privileges on *.* to fooher_login @'127.0.0.1' identified by "www.fooher.com" with grant option;"
30. 修改 localhost 密码
mysql -uroot -e "grant all privileges on *.* to fooher_login@'localhost' identified by "www.fooher.com" with grant option;"
31. 创建用户
MySQL 数据库用户名 dbrootname='fooher_user' MySQL 数据库密码 dbrootpwd='8%jMrwqwSZ6u@4' MySQL 数据库名 db_name='data_db_fooher'
32. 添加本地账号
mysql -uroot -e "CREATE USER ${dbrootname}@'127.0.0.1' IDENTIFIED by \"${dbrootpwd}\";
33. 添加远程访问账号
mysql -uroot -e "grant all privileges on *.* to ${dbrootname}@'%' identified by \"${dbrootpwd}\" with grant option;
34. 创建数据库
mysql -uroot -e "create database IF NOT EXISTS ${db_name} default character set utf8mb4 collate utf8mb4_unicode_ci;
35. 授权 fooher_user 用户拥有 data_db_fooher 数据库的所有权限
mysql -uroot -e "grant all privileges on ${db_name}.* to '${dbrootname}'@'127.0.0.1' identified by \"${dbrootpwd}\" with grant option;
36. 刷新数据库
mysql -u"${fooher_login}" -p"${dbrootpwd}" -e "flush privileges;
37. 还原数据库
mysql -u"${fooher_login}" -p"${dbrootpwd}" -hlocalhost ${db_name} < fooherdb.sql
- 到此 MySQL 数据库安装完成 ,大家根据自己需求进行操作 By 运维天涯