MySQL 系列教程(一) 生产标准线上环境安装配置案例及棘手问题解决
一、简介
MySQL是最流行的开放源码SQL数据库管理系统,它是由MySQL AB公司开发、发布并支持的。有以下特点:
- MySQL是一种数据库管理系统。
- MySQL是一种关联数据库管理系统。
- MySQL软件是一种开放源码软件。
- MySQL数据库服务器具有快速、可靠和易于使用的特点。
- MySQL服务器工作在客户端/服务器模式下,或嵌入式系统中。
- 有大量可用的共享MySQL软件。
MySQL表最大能达到多少?
InnoDB(支持事务:也就是当出错的情况下可以进行回滚)
InnoDB存储引擎将InnoDB表保存在一个表空间内,该表空间可由数个文件创建。这样,表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区,从而使得很大的表成为可能。表空间的最大容量为64TB
二、MySQL的安装
下载地址:自己百度
centos安装方法:
1 | yum install mysql - server |
Ubuntu 安装:
1 2 3 4 5 6 | 1. sudo apt - get install mysql - server 2. sudo apt - get isntall mysql - client 3. sudo apt - get install libmysqlclient - dev # 检测是否安装成功(是否为LISTEN状态) sudo netstat - tap | grep mysql |
编译安装MySQL-5.5.32:
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 | # 安装依赖包 yum install ncurses - devel gcc gcc - c + + - y # 创建目录 mkdir - p / home / oldsuo / tools # 安装cmake软件,gmake编译安装 cd / home / oldsuo / tools / tar xf cmake - 2.8 . 8.tar .gz cd cmake - 2.8 . 8 . / configure #CMake has bootstrapped. Now run gmake. gmake gmake install cd .. / # 开始安装mysql # 创建用户和组 groupadd mysql useradd mysql - s / sbin / nologin - M - g mysql # 解压编译MySQL tar zxf mysql - 5.5 . 32.tar .gz cd mysql - 5.5 . 32 cmake . - DCMAKE_INSTALL_PREFIX = / application / mysql - 5.5 . 32 \ - DMYSQL_DATADIR = / application / mysql - 5.5 . 32 / data \ - DMYSQL_UNIX_ADDR = / application / mysql - 5.5 . 32 / tmp / mysql.sock \ - DDEFAULT_CHARSET = utf8 \ - DDEFAULT_COLLATION = utf8_general_ci \ - DEXTRA_CHARSETS = gbk,gb2312,utf8,ascii \ - 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 #-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32 提示: 编译时可配置的选项很多,具体可参考结尾附录或官方文档: make #[100%] Built target my_safe_process make install ln - s / application / mysql - 5.5 . 32 / / application / mysql 如果上述操作未出现错误,则MySQL5. 5.32 软件cmake方式的安装就算成功了。 #拷贝配置文件 cp mysql - 5.5 . 32 / support - files / my - small.cnf / etc / my.cnf #添加变量,并使之生效 echo 'export PATH=/application/mysql/bin:$PATH' >> / etc / profile source / etc / profile echo $PATH #授权用户及/tmp/临时文件目录 chown - R mysql.mysql / application / mysql / data / chmod - R 1777 / tmp / #初始化数据库 cd / application / mysql / scripts / . / mysql_install_db - - basedir = / application / mysql / - - datadir = / application / mysql / data / - - user = mysql cd .. / #启动数据库 cp support - files / mysql.server / etc / init.d / mysqld chmod + x / etc / init.d / mysqld / etc / init.d / mysqld start #检查端口 netstat - lntup|grep 3306 |
编译安装完后一般安全操作:
1、删除不必要的用户和库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | #查看用户和主机列,从mysql.user里查看 select user,host from mysql.user; #删除用户名为空的库,并检查 delete from mysql.user where user = ''; select user,host from mysql.user; #删除主机名为localhost.localdomain的库,并检查 delete from mysql.user where host = 'localhost.localdomain' ; select user,host from mysql.user; #删除主机名为::1的库,并检查。::1库的作用为IPV6 delete from mysql.user where host = '::1' ; #删除test库 drop database test; |
2、添加额外管理员:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # 添加额外管理员,system作为管理员,oldsuo为密码 mysql> delete from mysql.user; Query OK, 2 rows affected ( 0.00 sec) mysql> grant all privileges on * . * to system@ 'localhost' identified by 'oldsuo' with grant option; Query OK, 0 rows affected ( 0.00 sec) # 刷新MySQL的系统权限相关表,使配置生效 mysql> flush privileges; Query OK, 0 rows affected ( 0.00 sec) mysql> select user,host from mysql.user; + - - - - - - - - + - - - - - - - - - - - + | user | host | + - - - - - - - - + - - - - - - - - - - - + | system | localhost | + - - - - - - - - + - - - - - - - - - - - + row in set ( 0.00 sec) mysql> |
3、设置登录密码并开机自启:
1 2 3 4 5 6 7 | #设置密码,并登陆 / usr / local / mysql / bin / mysqladmin - u root password 'oldsuo' mysql - usystem - p #开机启动mysqld,并检查 chkconfig mysqld on chkconfig - - list mysqld |
#安装依赖包 yum –y install ncurses ncurses-devel gcc gcc-c++ #添加mysql用户及组 groupadd mysql useradd -r -s /sbin/nologin -g mysql mysql #mysql5.1.62编译参数: ./configure \ --prefix=/usr/local/mysql \ --with-unix-soket-path=/usr/local/tmp/mysql.sock \ --localstatedir=/usr/local/mysql/data \ --enable-assembler \ --enable-thread-safe-client \ --with-mysqld-user=mysql \ --with-big-tables \ --without-debug \ --with-pthread \ --enable-assembler \ --with-extra-charsets=complex \ --with-readline \ --with-ssl \ --with-embedded-server \ --enable-local-infile \ --with-plugins=partition,innobase \ --with-mysqld-ldflags=-all-static \ --with-client-ldflags=-all-static make && make install #初始化mysql mkdir -p /usr/local/mysql/data #建立mysql数据文件目录 chown -R mysql.mysql /usr/local/mysql/ #授权mysql用户访问mysql安装目录 /usr/local/mysql/bin/mysql_install_db --user=mysql #初始化 #拷贝mysql启动脚本 cp support-files/my-small.cnf /etc/my.cnf #cp support-files/mysql.server /etc/init.d/mysqld chmod 700 /etc/init.d/mysqld #配置mysql使用全局路径 echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile #添加变量到profile source /etc/profile #使变量生效 echo $PATH #检查 #启动mysqld /etc/init.d/mysqld start #登陆报错,做软链接 #ln -s /usr/local/mysql/bin/mysql /usr/bin/ #启动报错日志: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't #exist #解决方法: /usr/local/mysql/bin/mysql_install_db --user=mysql #初始化数据库即可 #登陆报错: mysql: unknown variable 'datadir=/usr/local/mysql/data' #解决方法: my.cnf 配置问题,vim /etc/my.cnf [client] #password = your_password port = 3306 socket = /tmp/mysql.sock #datadir = /data1/mysql/var/ #这个不能加在上面,去掉 [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /data1/mysql/var/ #加在这里就可以了 #设置mysql用户root 的密码为oldsuo /usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'
三、字符集
对于新手来说,字符集乱码问题无疑是头痛的问题,小编就带你不在头痛,从此幸福。
1、字符集简介:
字符集,character set,就是一套表示字符的符号和这些的符号的底层编码;而校验规则,则是在字符集内用于比较字符的一套规则。简单的说,字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2,MySQL数据库字符集包括字符集和校对规则两个概念,字符集是定义数据库里面的内容字符串的存储方式,而校对规则是定义比较字符串的方式。
建议:中英文环境选择utf8
2、查看设置字符集
1 2 3 4 5 6 7 8 9 10 11 | # 查看MySQL字符集设置情况 show variables like 'character_set%' ; # 查看库的字符集 show create database db; # 查看表的字符集 show create table db_tb\G # 查询所有 show collation; # 设置表的字符集 set tables utf8; |
show create database nick_defailt\G #查看nick_defailt库字符集 mysql -uroot -p -e "SHOW CHARACTER SET;" show variables like 'character_set%'; mysql> show variables like 'character_set%'; +-----------------------------------------+------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------+-------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/mysql/charsets/ | +----------------------------------------+--------------------------------------------------------------+ rows in set (0.00 sec) mysql> show create database nick_defailt \G *************************** 1. row *************************** Database: data Create Database: CREATE DATABASE `data` /*!40100 DEFAULT CHARACTER SET utf8 */ row in set (0.00 sec)
3、MySQL数据乱码及解决方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 1 > 系统方面 cat / etc / sysconfig / i18n LANG = "zh_CN.UTF-8" 2 > 客户端(程序),调整字符集为latin1。 mysql> set names latin1; #临时生效 Query OK, 0 rows affected ( 0.00 sec) #更改my.cnf客户端模块的参数,实现set name latin1 的效果,并且永久生效。 [client] default - character - set = latin1 #无需重启服务,退出登录就生效,相当于set name latin1。 3 > 服务端,更改my.cnf参数 [mysqld] default - character - set = latin1 #适合5.1及以前版本 character - set - server = latin1 #适合5.5 4 > 库、表、程序 #建表指定utf8字符集 mysql> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected ( 0.00 sec) |
4、将utf8字符集修改成GBK字符集的实际过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 1 > 导出表结构 #以utf8格式导出 mysqldump - uroot - p - - default - character - set = utf8 - d nick_defailt>alltable.sql - - default - character - set = gbk #表示已GBK字符集连接 –d 只表示表结构 2 > 编辑alltable.sql 将utf8改成gbk。 3 > 确保数据库不在更新,导出所有数据 mysqldump - uroot - p - - quick - - no - create - info - - extended - insert - - default - character - set = utf8 nick_defailt>alldata.sql 4 > 打开alldata.sql将 set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端) 5 > 建库 create database oldsuo default charset gbk; 6 > 创建表,执行alltable.sql mysql - uroot - p oldsuo <alltable.sql 7 > 导入数据 mysql - uroot - p oldsuo <alltable.sql |
四、存储引擎
MySQL最常用存储引擎Myisam和Innodb。mysql 5.5.5以后默认存储引擎为Innodb。
MySQL的每种引擎在MySQL里是通过插件的方式使用的,MySQL可以支持多种存储引擎。
建议:使用 Innodb引擎,因为支持回滚,后续博客会讲。
1、引擎对应系统文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 1 ) MyISAM引擎系统库表对应文件 [root@mysql 3306 ] # ll /data/3306/data/mysql/ - rw - rw - - - - . 1 mysql mysql 10630 10 月 31 16 : 05 user.frm #保存表的定义 - rw - rw - - - - . 1 mysql mysql 1140 10 月 31 18 : 40 user.MYD #数据文件 - rw - rw - - - - . 1 mysql mysql 2048 10 月 31 18 : 40 user.MYI #索引文件 [root@mysql 3306 ] # file data/mysql/user.frm data / mysql / user.frm: MySQL table definition file Version 9 [root@mysql 3306 ] # file data/mysql/user.MYD data / mysql / user.MYD: DBase 3 data file ( 167514107 records) [root@mysql 3306 ] # file data/mysql/user.MYI data / mysql / user.MYI: MySQL MISAM compressed data file Version 1 2 ) InnoDB引擎 [root@mysql 3306 ] # ll data/ - rw - rw - - - - . 1 mysql mysql 134217728 10 月 31 20 : 05 ibdata1 |
2、修改引擎
1 2 3 | 创建后引擎的修改 语法: ALTER TABLE student ENGINE = INNODB; ALTER TABLE student ENGINE = MyISAM; |
mysql> use teacher; Database changed mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ row in set (0.01 sec) mysql> ALTER TABLE student ENGINE = MyISAM; Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec)
3、建表指定引擎
1 2 3 4 5 6 7 | mysql> create table mess ( - > id int ( 4 ) not null, - > name char( 20 ) not null, - > age tinyint( 2 ) NOT NULL default '0' , - > dept varchar( 16 ) default NULL - > ) ENGINE = MyISAM CHARSET = utf8; Query OK, 0 rows affected ( 0.00 sec) |
五、基本语句命令
运行相关:
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 | 1 、 单实例mysql启动 [root@localhost ~] # /etc/init.d/mysqld start Starting MySQL [确定] #mysqld_safe –user=mysql & 2 、 查看MySQL端口 [root@localhost ~] # ss -lntup|grep 3306 tcp LISTEN 0 50 * : 3306 * : * users:(( "mysqld" , 19651 , 10 )) 3 、 查看MySQL进程 [root@localhost ~] # ps -ef|grep mysql|grep -v grep root 19543 1 0 Oct10 ? 00 : 00 : 00 / bin / sh / usr / local / mysql / bin / mysqld_safe - - datadir = / usr / local / mysql / data - - pid - file = / usr / local / mysql / data / localhost.localdomain.pid mysql 19651 19543 0 Oct10 ? 00 : 05 : 04 / usr / local / mysql / libexec / mysqld - - basedir = / usr / local / mysql - - datadir = / usr / local / mysql / data - - user = mysql - - log - error = / usr / local / mysql / data / localhost.localdomain.err - - pid - file = / usr / local / mysql / data / localhost.localdomain.pid - - socket = / tmp / mysql.sock - - port = 3306 4 、 MySQL启动原理 / etc / init.d / mysqld 是一个shell启动脚本,启动后最终会调用mysqld_safe脚本,最后调用mysqld服务启动mysql。 "$manager" \ - - mysqld - safe - compatible \ - - user = "$user" \ - - pid - file = "$pid_file" > / dev / null 2 >& 1 & 5 、关闭数据库 [root@localhost ~] # /etc/init.d/mysqld stop Shutting down MySQL.... [确定] 6 、 查看mysql数据库里操作命令历史 cat / root / .mysql_history 7 、 强制linux不记录敏感历史命令 HISTCONTROL = ignorespace 8 、 mysql设置密码 / usr / local / mysql / bin / mysqladmin - u root password 'oldsuo' 9 、 mysql修改密码,与多实例指定sock修改密码 mysqladmin - uroot - passwd password 'oldsuo' mysqladmin - uroot - passwd password 'oldsuo' - S / data / 3306 / mysql.sock |
操作相关:
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 | #登陆mysql数据库 mysql - uroot –p #查看有哪些库 show databases; #删除test库 drop database test; #使用test库 use test; #查看有哪些表 show tables; #查看suoning表的所有内容 select * from suoning; #查看当前版本 select version(); #查看当前用户 select user(); #查看用户和主机列,从mysql.user里查看 select user,host from mysql.user; #删除前为空,后为localhost的库 drop user ""@localhost; #刷新权限 flush privileges; #跳出数据库执行命令 system ls; |
六、破解mysql登录密码
忘记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 | 1 > 普通方式 #> service mysqld stop #>mysqld_safe --skip-grant-tables & 输入 mysql - uroot - p 回车进入 >use mysql; > update user set password = PASSWORD( "newpass" )where user = "root" ; 更改密码为 newpassord > flush privileges; 更新权限 > quit 退出 service mysqld restart mysql - uroot - p新密码进入 2 > 普通方式的简写 service mysqld stop mysqld_safe - - skip - grant - tables - - user = mysql & mysql update mysql.user set password = PASSWORD( "newpass" )where user = "root" and host = 'localhost' ; flush privileges; mysqladmin - uroot - pnewpass shutdown / etc / init.d / mysqld start mysql - uroot - pnewpass #登陆 3 >多实例方式 killall mysqld mysqld_safe –defaults - file = / data / 3306 / my.cnf –skip - grant - table & mysql –u root –p –S / data / 3306 / mysql.sock #指定sock登陆 update mysql.user set password = PASSWORD( "newpass" )where user = "root" ; flush privileges; mysqladmin - uroot - pnewpass shutdown / etc / init.d / mysqld start mysql - uroot - pnewpass #登陆 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 《HelloGitHub》第 106 期
· 数据库服务器 SQL Server 版本升级公告
· C#/.NET/.NET Core技术前沿周刊 | 第 23 期(2025年1.20-1.26)