一、MySQL介绍
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性
特点：
性能卓越，服务稳定，很少出现宕机
开放源代码且无版权制约，自主性强，使用成本低
支持多操作系统，提供多种API接口，支持多种开发语言(Python,Php,Java等)
二、MySQL安装方式
1．yum安装 (适合对数据库要求不太高的场合)
大的门户网站把源码根据企业需求制作成rpm，搭建yum仓库
2．常规方式编译安装
.configure/make/make install (mysql 5.1及以前)
cmake /make /make install
| 序号 | MySQL安装方式 | 特点说明 |
| 1 | yum/rpm安装 | 简单，部署速度快，但是没法定制安装，适合新手 |
| 2 | 二进制安装 | 解压软件，简单配置后就可以使用，不用安装，部署速度较快，专业DBA常用这种方式，软件名如：mysql-5.5.50-linux2.6-x86_64.tar.gz |
| 3 | 源码编译安装 | 可以定制安装(指定字符集，安装路径等)，但是安装时间长 |
| 4 | 源码软件结合yum/rpm安装 | 把源码软件制作成符合要求的rpm,放到yum仓库，然后通过yum来安装，结合了1和3的优点，安装快速，可以任意定制参数 |
三、MySQL安装实战(源码cmake方式编译安装MySQL)
1．下载mysql/cmake安装包
| 1 2 3 | [root@master ~] # mkdir /home/tools [root@master ~] # wget –P /home/tools [root@master ~] # wget -P /home/tools/ |
2．建立账号
| 1 2 3 4 5 6 | [root@master ~] # groupadd mysql [root@master ~] # useradd -s /sbin/nologin -g mysql -M mysql useradd 参数说明： -s /sbin/nologin # 表示禁止该用户登录，只需角色存在即可，加强安全 -g mysql # 指定属组 -M # 表示不创建用户家目录 |
3．配置安装环境
创建目录并授权
| 1 2 3 4 | [root@master ~] # mkdir -p /usr/local/mysql [root@master ~] # mkdir -p /db/mysql [root@master ~] # chown -R mysql.mysql /usr/local/mysql [root@master ~] # chown -R mysql.mysql /db |
安装依赖
| 1 | [root@master ~] # yum install gcc gcc-c++ make cmake ncurses-devel bison perl -y |
配置解析
| 1 2 3 4 | [root@master ~] # hostname master.opsedu.com [root@master ~] # vim /etc/hosts 192.168.10.66 master # 添加一条 |
4．安装mysql
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [root@master tools] # tar -zxf mysql-5.5.50.tar.gz [root@master tools] # cd mysql-5.5.50 [root@master mysql-5.5.50] # cmake \ > -DCMAKE_INSTALL_PREFIX= /usr/local/mysql/ \ > -DMYSQL_DATADIR= /db/mysql \ > -DMYSQL_TCP_PORT=3306 \ > -DDEFAULT_CHARSET=utf8 \ > -DDEFAULT_COLLATION=utf8_general_ci \ > -DEXTRA_CHARSETS=all \ > -DENABLED_LOCAL_INFILE=ON \ > -DWITH_INNOBASE_STORAGE_ENGINE=1 \ > -DWITH_FEDERATED_STORAGE_ENGINE=1 \ > -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ > -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ > -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ > -DWITH_FAST_MUTEXES=1 \ > -DWITH_ZLIB=bundled \ > -DENABLED_LOCAL_INFILE=1 \ > -DWITH_READLINE=1 \ > -DWITH_EMBEDDED_SERVER=1 \ > -DWITH_DEBUG=0 \ > -DMYSQL_UNIX_ADDR= /tmp/mysql .sock [root@master mysql-5.5.50] # make # 编译 [root@master mysql-5.5.50] # make install |
5．初始化mysql
配置mysql环境变量
| 1 2 | [root@master mysql-5.5.50] # echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile [root@master mysql-5.5.50] # source /etc/profile |
查看配置文件
| 1 2 3 4 5 6 7 | [root@master mysql-5.5.50] # ll support-files/*.cnf -rw-r--r-- 1 root root 4667 Jul9 01:26 support-files /my-huge .cnf -rw-r--r-- 1 root root 19759 Jul 9 01:26 support-files /my-innodb-heavy-4G .cnf -rw-r--r-- 1 root root 4641 Jul9 01:26 support-files /my-large .cnf -rw-r--r-- 1 root root 4652 Jul9 01:26 support-files /my-medium .cnf -rw-r--r-- 1 root root 2816 Jul9 01:26 support-files /my-small .cnf [root@master mysql-5.5.50] # /bin/cp support-files/my-small.cnf /etc/my.cnf # copy配置文件 |
创建mysql数据库文件
| 1 | [root@master mysql-5.5.50] # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/db/mysql --user=mysql |
以上命令主要是生成mysql库及相关文件：
这些文件是mysql正确运行所必需的基本数据库文件，其功能是对mysql权限、状态等进行管理
启动mysql
| 1 2 3 | [root@master mysql-5.5.50] # /usr/local/mysql/bin/mysqld_safe --user=mysql & [root@master mysql-5.5.50] # netstat -lnt|grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN |
配置mysql开机启动
| 1 2 3 4 | [root@master mysql-5.5.50] # cp support-files/mysql.server /etc/init.d/mysqld [root@master mysql-5.5.50] # chmod 700 /etc/init.d/mysqld [root@master mysql-5.5.50] # chkconfig --add mysqld [root@master mysql-5.5.50] # chkconfig mysqld on |
初始化遇见错误
示例1:
WARING:The host ‘mysql’ could not be locked up with resolveip
需要修改主机名的解析，使其和usernae –n一样
示例2:
ERROR:1004 Can’t create file ‘/tmp/#sql300e_1_0.frm’(errno:13)
原因是/tmp权限有问题(不解决，后面可能无法登陆数据库)
6．后续操作
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [root@master mysql-5.5.50] # mysql # mysql安装好后，默认没有密码 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.50 Source distribution Copyright (c) 2000, 2016, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and /or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> # 为mysql增加(设定)密码 [root@master mysql-5.5.50] # mysqladmin -uroot password 'q.123456' |
7. 单实例MySQL自动部署脚本
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 | #!/bin/env python # -*- coding:utf-8 -*- ''' MySQL部署py ''' import os, sys, subprocess import time, datetime import socket import shutil class MySQL_init: ''' MySQL编译安装py ''' def __init__( self ): self .url = '' # mysql的编译参数，写入到文件中，放在ftp上 self .ret = [] self . compile = [] self .count = 0 self .status = ' ' @staticmethod def get_local_ip(): ''' 获取服务器IP，如果有公网地址就取公网IP，没有公网地址就取私网IP :return: ''' try : sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) sock.connect(( '8.8.8.8' , 80 )) (addr, port) = sock.getsockname() sock.close() return addr except socket.error: return "127.0.0.1" def install_check( self ): ''' 检查命令是否执行成功 :return: True:成功 False:失败(每个方法最后统一检查) ''' for i in self .ret: self .count + = i if self .count = = 0 : print ( '#INFO ==================== \033[1;32mThe command execution success.\033[0m ====================' ) time.sleep( 3 ) return True else : print ( '#INFO ==================== \033[1;31mThe command execution failed.\033[0m ====================' ) time.sleep( 3 ) return False def install_cmd( self , arg): ''' 收集shell命令执行结果 :param arg: 命令字符串 :return: 返回执行状态 ''' val = subprocess.call(arg, shell = True , stdout = subprocess.PIPE) self .ret.append(val) return val def MySQL_download( self ): ''' 下载安装包 :return:False:下载执行失败 ''' print '#INFO ==================== \033[1;33mBegan to download MySQL installation package\033[0m ====================' time.sleep( 3 ) try : os.makedirs( '/home/tools' ) except OSError as e: pass os.chdir( '/home/tools' ) self .install_cmd( 'wget -c %s/mysql-5.5.50.tar.gz' % self .url) if self .install_check() = = False : return False def MySQL_add_acc( self ): ''' 添加账号 :return:True:账号已存在 False:账号添加失败 ''' print '#INFO ==================== \033[1;33mBegin creating MySQL account\033[0m ====================' time.sleep( 3 ) self .acc = raw_input ( '请输入要创建账号名:' ).strip() self .status = subprocess.call( 'id %s > /dev/null 2>1' % self .acc,shell = True ) if self .status = = 0 : print ( '#INFO ==================== \033[1;32mThe command execution success.\033[0m ====================' ) time.sleep( 3 ) return True else : self .install_cmd( 'groupadd {user} && useradd -s /sbin/nologin -g {user} -M {user}' . format (user = self .acc)) if self .install_check() = = False : return False def MySQL_conf_env( self ): ''' 配置mysql安装环境 :return: ''' print '#INFO ==================== \033[1;33mStart configuring the MySQL installation environment\033[0m ====================' time.sleep( 3 ) self .install_path = raw_input ( '请输入一个不存在的目录(安装目录):' ).strip() self .data_path = raw_input ( '请输入一个不存在的目录(数据存放目录):' ).strip() if os.path.isdir( self .install_path) and os.path.isdir( self .data_path): pass else : try : os.makedirs( self .install_path) os.makedirs( self .data_path) except OSError as e: pass self .install_cmd( 'chown -R {user}.{user} {path1} {path2}' . format (user = self .acc, path1 = self .install_path,path2 = self .data_path)) ret = subprocess.call( 'yum -y install gcc gcc-c++ make cmake ncurses-devel bison perl' , shell = True ) if self .install_check() = = True and ret = = 0 : pass else : return False self .hostname = socket.getfqdn(socket.gethostname()) # self.ip = socket.gethostbyname(socket.gethostname()) # 不知怎么回事，不同linux OS,有时可以获取到IP，有时会报‘socket.gaierror’错误 self .ip = MySQL_init.get_local_ip() with open ( '/etc/hosts' , 'a' ) as f: f.write( '\n{ip}\t{hostname}' . format (ip = self .ip, hostname = self .hostname)) def MySQL_install( self ): ''' 安装MySQL :return: ''' print '#INFO ==================== \033[1;33mStart installing MySQL\033[0m ====================' time.sleep( 3 ) os.chdir( '/home/tools' ) self .install_cmd( 'tar zxf mysql-5.5.50.tar.gz' ) os.chdir( 'mysql-5.5.50' ) self .install_cmd( 'wget -c {url}/compile.conf' . format (url = self .url)) with open ( 'compile.conf' , 'r+' ) as f: for lines in f: self . compile .append(lines.strip()) self . compile [ 0 ] = '-DCMAKE_INSTALL_PREFIX=%s' % self .install_path self . compile [ 1 ] = '-DMYSQL_DATADIR=%s' % self .data_path self .status = ' ' self . compile = self .status.join( self . compile ) rets = subprocess.call( 'cmake %s' % self . compile , shell = True ) vals = subprocess.call( 'make -j 2 && make install' , shell = True ) if rets = = vals = = 0 : pass else : return False os.remove( 'compile.conf' ) if self .install_check() = = False : return False def MySQL_env_init( self ): ''' 初始化mysql :return: ''' print '#INFO ==================== \033[1;33mStart initialized MySQL\033[0m ====================' time.sleep( 3 ) self .install_cmd( "echo 'export PATH={ins_path}/bin:$PATH' >>/etc/profile" . format (ins_path = self .install_path)) self .install_cmd( 'source /etc/profile' ) shutil.copyfile( 'support-files/my-small.cnf' , '/etc/my.cnf' ) self .install_cmd( '{path1}/scripts/mysql_install_db --basedir={path1} --datadir={path2} --user={user}' . format (path1 = self .install_path, path2 = self .data_path, user = self .acc)) if self .install_check() = = False : return False def MySQL_boot( self ): ''' 启动MySQL :return: ''' print '#INFO ==================== \033[1;33mStarting MySQL...\033[0m ====================' self .install_cmd( '{path}/bin/mysqld_safe --user={user} &' . format (path = self .install_path, user = self .acc)) print '\r' time.sleep( 5 ) rests = self .install_cmd( 'netstat -an | grep 3306 >/dev/null' ) if rests = = 0 : pass else : return False self .install_cmd( '/bin/cp support-files/mysql.server /etc/init.d/mysqld' ) self .install_cmd( 'chmod 700 /etc/init.d/mysqld' ) self .install_cmd( 'chkconfig --add mysqld && chkconfig mysqld on' ) if self .install_check() = = False : return False else : print '#INFO \033[1;32mMySQL installation was successful.\033[0m' def main( self ): ret = MySQL_init() if ret.MySQL_download() = = False : print '\033[1;31m#INFO ==========> MySQL 安装包下载失败\033[0m.' sys.exit( 1 ) elif ret.MySQL_add_acc() = = False : print '\033[1;31m#INFO ==========> MySQL 账号创建失败\033[0m.' sys.exit( 2 ) elif ret.MySQL_conf_env() = = False : print '\033[1;31m#INFO ==========> MySQL 安装环境配置失败\033[0m.' sys.exit( 3 ) elif ret.MySQL_install() = = False : print '\033[1;31m#INFO ==========> MySQL 安装失败\033[0m.' sys.exit( 4 ) elif ret.MySQL_env_init() = = False : print '\033[1;31m#INFO ==========> MySQL 初始化失败\033[0m.' sys.exit( 4 ) elif ret.MySQL_boot() = = False : print '\033[1;31m#INFO ==========> MySQL 启动失败\033[0m.' sys.exit( 5 ) if __name__ = = '__main__' : val = MySQL_init() val.main() |