返回
基础
分类

固然MySQL的源码编写翻译挺轻巧的

日期: 2020-01-02 08:15 浏览次数 : 118

有过MySQL运维的人应该都清楚,线上的MySQL一般都采用源码编译,因为这样才可以根据企业的各自需要选择要编译的功能,虽然MySQL的源码编译挺简单的,但是试想一下,如果你有几百台服务器同时要安装MySQL,难道你还一台台去手动编译、编写配置文件吗?这显然太低效了,本文讨论MySQL的自动化安装部署。

mysql5.5.17源码安装

  1. 源码包下载
    源码包通常也采用tar.gz压缩,名称中只包含版本信息,大小也比RPM包、二进制包小很多,解压后的文件中含有INSTALL-SOURCE文件,可从MySQL官网(
  2. CMake
    在采用源码包安装MySQL实例之前,先来介绍一下cmake这个编译工具。在MySQL 5.5之前,是采用configure工具执行源码编译的,到了MySQL 5.5,改用cmake进行编译。这是一个比make更高级的编译配置工具,可根据不同平台、不同编译器,生产相应的Makefile或者vcproj项目,所以需要首先从官网(
    安装cmake之前安装gcc包
    rpm -ivh kernel-headers-2.6.18-308.el5.x86_64.rpm
    rpm -ivh glibc-headers-2.5-81.x86_64.rpm
    rpm -ivh glibc-devel-2.5-81.x86_64.rpm
    rpm -ivh gcc-4.1.2-52.el5.x86_64.rpm
    rpm -ivh libstdc++-devel-4.1.2-52.el5.x86_64.rpm
    rpm -ivh gcc-c++-4.1.2-52.el5.x86_64.rpm
    安装cmake
    /mysql/cmake-2.8.3
    ./configure
    gmake && make install
  1. 安装mysql 5.5.17
  2. 创建mysql系统组及用户
    groupadd mysql
    useradd –g mysql mysql

  3. 设置用户操作系统资源限制

vi /etc/security/limits.conf
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536
安装需要包
# rpm -ivh ncurses-devel-5.5-24.20060715.x86_64.rpm
# rpm -ivh bison-2.3-2.1.x86_64.rpm

  1. 安装MYSQL Server
    #mkdir /opt/mysql
    #chown -R mysql:mysql /opt/mysql
    #gunzip mysql-5.5.17.tar.gz
    #tar xvf mysql-5.5.17.tar
    #cd mysql-5.5.17
    # cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql
    > -DMYSQL_USER=mysql
    > -DMYSQL_TCP_PORT=3306
    > -DMYSQL_DATADIR=/opt/mysql/data
    > -DWITH_MYISAM_STORAGE_ENGINE=1
    > -DWITH_INNOBASE_STORAGE_ENGINE=1
    > -DWITH_ARCHIVE_STORAGE_ENGINE=1
    > -DWITH_MEMORY_STORAGE_ENGINE=1
    > -DWITH_BLACKHOLE_STORAGE_ENGINE=1
    > -DWITH_PARTITION_STORAGE_ENGINE=1
    > -DENABLED_LOCAL_INFILE=1
    > -DWITH_READLINE=1
    > -DWITH_SSL=yes
    > -DDEFAULT_CHARSET=utf8
    > -DDEFAULT_COLLATION=utf8_general_ci
    > -DEXTRA_CHARSETS=all

#make
#make install

初始化DB
# sh scripts/mysql_install_db --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data
Installing MySQL system tables...
必赢手机登录网址 ,OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/opt/mysql/bin/mysqladmin -u root password 'new-password'
/opt/mysql/bin/mysqladmin -u root -h mysql password 'new-password'

Alternatively you can run:
/opt/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /opt/mysql ; /opt/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /opt/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /opt/mysql/scripts/mysqlbug script!

  1. 启动Mysql Sever
    配置service服务
    # cp /opt/mysql/files/mysql.server /etc/init.d/mysql --配置basedir、datadir
    配置mysql参数文件my.cnf

#vi /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /opt/mysql/data/mysql.sock

[mysqld]
port = 3306
server-id = 24
datadir = /opt/mysql/data
socket = /opt/mysql/data/mysql.sock
pid-file = /opt/mysql/data/mysql.pid
character-set-server = utf8
default_storage_engine = InnoDB
log-bin = /opt/mysql/data/mysql-bin
binlog_format = row
sync-binlog = 1
slow-query-log = on
slow-query-log-file = /opt/mysql/data/mysql-slow.log
log_error = /opt/mysql/data/mysql.err
max_connections = 2000
back_log = 50
skip-external-locking
skip-name-resolve

key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 2000
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 8

innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

"/etc/my.cnf" [New] 62L, 1531C written
[[email protected] support-files]# more /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /opt/data/mysql.sock

[mysqld]
port = 3306
server-id = 24
datadir = /opt/mysql/data
socket = /opt/mysql/data/mysql.sock
pid-file = /opt/mysql/data/mysql.pid
character-set-server = utf8
default_storage_engine = InnoDB
log-bin = /opt/mysql/data/mysql-bin
binlog_format = row
sync-binlog = 1
slow-query-log = on
slow-query-log-file = /opt/mysql/data/mysql-slow.log
log_error = /opt/mysql/data/mysql.err
max_connections = 2000
back_log = 50
skip-external-locking
skip-name-resolve

key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 2000
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 8

innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# service mysql start
Starting MySQL....[ OK ]

1. 源码包下载 源码包通常也采用tar.gz压缩,名称中只包含版本信息,大小也比RPM包、二进制包小很多,解压后的文件中含...

1、制作符合自己需求的RPM包

我们要根据MySQL的源码编译符合企业需求的RPM包,源码获取命令如下:

wget
tar -zxvf mysql-5.6.16.tar.gz
cd mysql-5.6.16
mkdir rpm
cd rpm

在上面我们获取了源码,并在源码主目录下创建rpm目录,接着我们在该目录下创建mysql.spec文件:

Name: mysql
Version:5.6.16
Release: guahao
License: GPL
URL:
Group: applications/database
BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-root
BuildRequires: cmake
Packager: [email protected]
Autoreq: no
prefix: /opt/mysql
Summary: MySQL 5.6.16

%description
The MySQL(TM) software delivers a very fast, multi-threaded, multi-user,
and robust SQL (Structured Query Language) database server. MySQL Server
is intended for mission-critical, heavy-load production systems as well
as for embedding into mass-deployed software.

%define MYSQL_USER mysql
%define MYSQL_GROUP mysql
%define __os_install_post %{nil}

%build
cd $OLDPWD/../
CFLAGS="-O3 -g -fno-exceptions -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
CXX=g++
CXXFLAGS="-O3 -g -fno-exceptions -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
export CFLAGS CXX CXXFLAGS

cmake .                                                 
  -DSYSCONFDIR:PATH=%{prefix}                           
  -DCMAKE_INSTALL_PREFIX:PATH=%{prefix}                 
  -DCMAKE_BUILD_TYPE:STRING=Release                     
  -DENABLE_PROFILING:BOOL=ON                            
  -DWITH_DEBUG:BOOL=OFF                                 
  -DWITH_VALGRIND:BOOL=OFF                              
  -DENABLE_DEBUG_SYNC:BOOL=OFF                          
  -DWITH_EXTRA_CHARSETS:STRING=all                      
  -DWITH_SSL:STRING=bundled                             
  -DWITH_UNIT_TESTS:BOOL=OFF                            
  -DWITH_ZLIB:STRING=bundled                            
  -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON               
  -DWITH_INNOBASE_STORAGE_ENGINE:BOOL=ON                
  -DWITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON                 
  -DWITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON               
  -DWITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON              
  -DDEFAULT_CHARSET=utf8                                
  -DDEFAULT_COLLATION=utf8_general_ci                   
  -DWITH_EXTRA_CHARSETS=all                             
  -DENABLED_LOCAL_INFILE:BOOL=ON                        
  -DWITH_EMBEDDED_SERVER=0                              
  -DINSTALL_LAYOUT:STRING=STANDALONE                    
  -DCOMMUNITY_BUILD:BOOL=ON                             
  -DMYSQL_SERVER_SUFFIX='-r5436';

make -j `cat /proc/cpuinfo | grep processor| wc -l`

%install
cd $OLDPWD/../
make DESTDIR=$RPM_BUILD_ROOT install

%clean
rm -rf $RPM_BUILD_ROOT

%files
%defattr(-, %{MYSQL_USER}, %{MYSQL_GROUP})
%attr(755, %{MYSQL_USER}, %{MYSQL_GROUP}) %{prefix}/*

%pre

%post
ln -s %{prefix}/lib %{prefix}/lib64

%preun

%changelog
有了这个spec文件之后,就可以执行如下命令生成我们自己的RPM包:
rpmbuild -bb ./mysql.spec

2、编写my.cnf模板

my.cnf模板如下:

[mysqld_safe]
pid-file=/opt/mysql/run/mysqld.pid

[mysql]
prompt=\[email protected]\d \r:\m:\s>
default-character-set=gbk
no-auto-rehash

[client]
socket=/opt/mysql/run/mysql.sock

[mysqld]
#dir
basedir=/opt/mysql
datadir=/data/mysql/data
tmpdir=/data/mysql/tmp
log-error=/data/mysql/log/alert.log
slow_query_log_file=/data/mysql/log/slow.log
general_log_file=/data/mysql/log/general.log
socket=/opt/mysql/run/mysql.sock

#innodb
innodb_data_home_dir=/data/mysql/data
innodb_log_group_home_dir=/data/mysql/data
innodb_data_file_path=ibdata1:2G;ibdata2:16M:autoextend
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=4
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=200M
innodb_flush_log_at_trx_commit=1
innodb_additional_mem_pool_size=20M
innodb_max_dirty_pages_pct=60
innodb_io_capacity=200
innodb_thread_concurrency=32
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_change_buffering=all
innodb_adaptive_flushing=1
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_read_ahead=0
innodb_use_native_aio=0
innodb_lock_wait_timeout=50
innodb_rollback_on_timeout=0
innodb_purge_threads=1
innodb_strict_mode=1
transaction-isolation=READ-COMMITTED

#myisam
key_buffer_size=100M
myisam_sort_buffer_size=64M
concurrent_insert=2
delayed_insert_timeout=300

#replication
master-info-file=/data/mysql/log/master.info
relay-log=/data/mysql/log/mysql-relay
relay_log_info_file=/data/mysql/log/mysql-relay.info
relay-log-index=/data/mysql/log/mysql-relay.index
slave_load_tmpdir=/data/mysql/tmp
slave_type_conversions="ALL_NON_LOSSY"
slave_net_timeout=4
skip-slave-start
sync_master_info=1000
sync_relay_log_info=1000

#binlog
log-bin=/data/mysql/log/mysql-bin
server_id=2552763370
binlog_cache_size=32K
max_binlog_cache_size=2G
max_binlog_size=500M
binlog_format=ROW
sync_binlog=1000
log-slave-updates=1
expire_logs_days=0

#server
default-storage-engine=INNODB
character-set-server=gbk
lower_case_table_names=1
skip-external-locking
open_files_limit=65536
safe-user-create
local-infile=1
performance_schema=0

log_slow_admin_statements=1
log_warnings=1
long_query_time=1
slow_query_log=1
general_log=0

query_cache_type=0
query_cache_limit=1M
query_cache_min_res_unit=1K

table_definition_cache=65536

thread_stack=512K
thread_cache_size=256
read_rnd_buffer_size=128K
sort_buffer_size=256K
join_buffer_size=128K
read_buffer_size=128K

port=3306
skip-name-resolve
skip-ssl
max_connections=4500
max_user_connections=4000
max_connect_errors=65536
max_allowed_packet=128M
connect_timeout=8
net_read_timeout=30
net_write_timeout=60
back_log=1024

#server id

细心的读者应该会注意在,在my.cnf的末尾在server id上留了空白,在后面的shell脚本会动态加上,这是因为在一个企业内部的所有MySQL的server id必须保持全局一致性,这样在主备复制时才不会导致混乱。
其实如果想把这个脚本写的更通用,完全可以把更多的参数留白,如port、datadir、内存相关参数等,这里我只是以server id为例,抛砖引玉。

3、准备MySQL数据目录模板

你得事先准备一台MySQL,可以根据自己的需求,把通用性的东西放在上面(如账户等),下面是一个最简单的已安装好的MySQL的数据目录结构:

[[email protected] mysql]# ls -l
total 12
drwxr-xr-x 5 mysql mysql 4096 Jul  2 09:26 data
drwxr-xr-x 2 mysql mysql 4096 Jul  1 18:21 log
drwxr-xr-x 2 mysql mysql 4096 Jul  2 09:26 tmp
[[email protected] mysql]# cd data
[[email protected] data]# ls -l
total 6314044
drwx------ 2 mysql mysql       4096 Jul  1 17:17 mysql
drwx------ 2 mysql mysql       4096 Jul  1 17:17 performance_schema
drwx------ 2 mysql mysql       4096 Jul  1 17:17 test

把该目录用tar打包(命名为data.tar),然后以这个为模板解压至新装MySQL实例的数据目录下即可。
4、编写自动化安装部署脚本

在运行这个脚本之前,我们必须得把前面几部制作的rpm包、my.cnf模板和数据目录模板放到一个固定的地方,本例中是放在企业内部的ftp上。

MySQL自动化安装部署脚本(命名为:mysql_install.sh)如下:

#!/bin/sh

#Step 1: Prepare
yum install cmake gcc g++ bison ncurses-devel zlib

groupadd mysql
useradd -g mysql mysql

#Step 2: Get Source
ftp -n<<EOF
open 10.10.100.254
user zhuxianjie zxj321
binary
cd mysql
prompt
mget *
EOF

#Step 3: Install
unique_id=`date "+%Y%m%d%M%S"`
echo 'server_id='$unique_id >> my.cnf
rpm -ivh mysql-5.6.16-guahao.x86_64.rpm
cp my.cnf /opt/mysql
chown -R mysql:mysql /opt/mysql

tar xvf data.tar -C /data
chown -R mysql:mysql /data/mysql

#step 4: Start MySQL
cp /opt/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig mysqld on

/etc/init.d/mysqld start

...