你的位置:首页 > 数据库

[数据库]屌炸天实战 Mysql 系列教程(一) 生产标准线上环境安装配置案例及棘手问题解决


一、简介

MySQL是最流行的开放源码SQL数据库管理系统,它是由MySQL AB公司开发、发布并支持的。有以下特点:

  • MySQL是一种数据库管理系统。
  • MySQL是一种关联数据库管理系统。
  • MySQL软件是一种开放源码软件。
  • MySQL数据库服务器具有快速、可靠和易于使用的特点。
  • MySQL服务器工作在客户端/服务器模式下,或嵌入式系统中。
  • 有大量可用的共享MySQL软件。

MySQL表最大能达到多少?

InnoDB存储引擎将InnoDB表保存在一个表空间内,该表空间可由数个文件创建。这样,表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区,从而使得很大的表成为可能。表空间的最大容量为64TB。

 

二、安装MySQL

下载MySQL地址:http://dev.mysql.com/downloads/mysql/

CentOS 安装:

yum install mysql-server

Ubuntu 安装:

1. sudo apt-get install mysql-server2. sudo apt-get isntall mysql-client3. sudo apt-get install libmysqlclient-dev# 检测是否安装成功(是否为LISTEN状态)sudo netstat -tap | grep mysql

编译安装MySQL-5.5.32:

# 安装依赖包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.gzcd cmake-2.8.8./configure#CMake has bootstrapped. Now run gmake.gmakegmake installcd ../# 开始安装mysql# 创建用户和组groupadd mysqluseradd mysql -s /sbin/nologin -M -g mysql# 解压编译MySQLtar zxf mysql-5.5.32.tar.gz cd mysql-5.5.32cmake . -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_processmake installln -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/profilesource /etc/profileecho $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=mysqlcd ../#启动数据库cp support-files/mysql.server /etc/init.d/mysqldchmod +x /etc/init.d/mysqld/etc/init.d/mysqld start#检查端口netstat -lntup|grep 3306

 编译安装完后一般安全操作:

 1、删除不必要的用户和库:

#查看用户和主机列,从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库的作用为IPV6delete from mysql.user where host='::1';#删除test库drop database test;

2、添加额外管理员:

# 添加额外管理员,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 |+--------+-----------+1 row in set (0.00 sec)mysql>

3、设置登录密码并开机自启:

#设置密码,并登陆/usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'mysql -usystem -p#开机启动mysqld,并检查chkconfig mysqld onchkconfig --list mysqld

 

#安装依赖包yum –y install ncurses ncurses-devel gcc gcc-c++ #添加mysql用户及组groupadd mysqluseradd -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-staticmake && make install#初始化mysqlmkdir -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    #添加变量到profilesource /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_passwordport        = 3306socket       = /tmp/mysql.sock#datadir       = /data1/mysql/var/     #这个不能加在上面,去掉[mysqld]port        = 3306socket       = /tmp/mysql.sockdatadir      = /data1/mysql/var/     #加在这里就可以了#设置mysql用户root 的密码为oldsuo/usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'

mysql5.1.62安装编译

 

 三、字符集

 对于新手来说,字符集乱码问题无疑是头痛的问题,小编就带你不在头痛,从此幸福。

1、字符集简介:

字符集,character set,就是一套表示字符的符号和这些的符号的底层编码;而校验规则,则是在字符集内用于比较字符的一套规则。简单的说,字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2,MySQL数据库字符集包括字符集和校对规则两个概念,字符集是定义数据库里面的内容字符串的存储方式,而校对规则是定义比较字符串的方式。

建议:中英文环境选择utf8

2、查看设置字符集

# 查看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/   |+----------------------------------------+--------------------------------------------------------------+8 rows in set (0.00 sec)mysql> show create database nick_defailt \G      *************************** 1. row ***************************    Database: dataCreate Database: CREATE DATABASE `data` /*!40100 DEFAULT CHARACTER SET utf8 */1 row in set (0.00 sec)

View Code

3、MySQL数据乱码及解决方法

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.54>  库、表、程序#建表指定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>  导出表结构#以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.sql4>  打开alldata.sql将set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端)5>  建库create database oldsuo default charset gbk;6>  创建表,执行alltable.sqlmysql -uroot -p oldsuo <alltable.sql7>  导入数据mysql -uroot -p oldsuo <alltable.sql

 

 四、存储引擎

MySQL最常用存储引擎Myisam和Innodb。mysql 5.5.5以后默认存储引擎为Innodb。

MySQL的每种引擎在MySQL里是通过插件的方式使用的,MySQL可以支持多种存储引擎。

建议:使用 Innodb引擎,因为支持回滚,后续博客会讲。

1、引擎对应系统文件

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.MYIdata/mysql/user.MYI: MySQL MISAM compressed data file Version 12)  InnoDB引擎[root@mysql 3306]# ll data/-rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1

2、修改引擎

创建后引擎的修改语法:  ALTER TABLE student ENGINE = INNODB;     ALTER TABLE student ENGINE = MyISAM;

mysql> use teacher;Database changedmysql> 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 |+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 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: 0mysql> 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 |+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改实例

3、建表指定引擎

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、  单实例mysql启动[root@localhost ~]# /etc/init.d/mysqld startStarting MySQL                       [确定]#mysqld_safe –user=mysql &2、  查看MySQL端口[root@localhost ~]# ss -lntup|grep 3306tcp  LISTEN   0   50           *:3306         *:*   users:(("mysqld",19651,10))3、  查看MySQL进程[root@localhost ~]# ps -ef|grep mysql|grep -v greproot   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.pidmysql  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=33064、  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 stopShutting down MySQL....                  [确定]6、  查看mysql数据库里操作命令历史cat /root/.mysql_history7、  强制linux不记录敏感历史命令HISTCONTROL=ignorespace8、  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

操作相关:

#登陆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> 普通方式#> 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 restartmysql -uroot -p新密码进入
2> 普通方式的简写service mysqld stopmysqld_safe --skip-grant-tables --user=mysql &mysqlupdate mysql.user set password=PASSWORD("newpass")where user="root" and host='localhost';flush privileges;mysqladmin -uroot -pnewpass shutdown/etc/init.d/mysqld startmysql -uroot -pnewpass #登陆3>多实例方式killall mysqldmysqld_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 startmysql -uroot -pnewpass #登陆

 

 注:本文有看不懂的在后续博客有详解