时间:2021-07-01 10:21:17 帮助过:12人阅读
- 1. sudo apt-get install mysql-<span style="color: #000000;">server
- </span>2. sudo apt-get isntall mysql-<span style="color: #000000;">client
- </span>3. sudo apt-get install libmysqlclient-<span style="color: #000000;">dev
- # 检测是否安装成功(是否为LISTEN状态)
- sudo netstat </span>-tap | grep mysql
编译安装MySQL-5.5.32:
- <span style="color: #008000;">#</span><span style="color: #008000;"> 安装依赖包</span>
- yum install ncurses-devel gcc gcc-c++ -<span style="color: #000000;">y
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建目录</span>
- mkdir -p /home/oldsuo/<span style="color: #000000;">tools
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 安装cmake软件,gmake编译安装</span>
- cd /home/oldsuo/tools/<span style="color: #000000;">
- tar xf cmake</span>-2.8.8<span style="color: #000000;">.tar.gz
- cd cmake</span>-2.8.8<span style="color: #000000;">
- .</span>/<span style="color: #000000;">configure
- </span><span style="color: #008000;">#</span><span style="color: #008000;">CMake has bootstrapped. Now run gmake.</span>
- <span style="color: #000000;">gmake
- gmake install
- cd ..</span>/
- <span style="color: #008000;">#</span><span style="color: #008000;"> 开始安装mysql</span><span style="color: #008000;">
- #</span><span style="color: #008000;"> 创建用户和组</span>
- <span style="color: #000000;">groupadd mysql
- useradd mysql </span>-s /sbin/nologin -M -<span style="color: #000000;">g mysql
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 解压编译MySQL</span>
- tar zxf mysql-5.5.32<span style="color: #000000;">.tar.gz
- cd mysql</span>-5.5.32<span style="color: #000000;">
- cmake . </span>-DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32<span style="color: #000000;"> </span>-DMYSQL_DATADIR=/application/mysql-5.5.32/<span style="color: #000000;">data </span>-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/<span style="color: #000000;">mysql.sock </span>-DDEFAULT_CHARSET=<span style="color: #000000;">utf8 </span>-DDEFAULT_COLLATION=<span style="color: #000000;">utf8_general_ci </span>-DEXTRA_CHARSETS=<span style="color: #000000;">gbk,gb2312,utf8,ascii </span>-DENABLED_LOCAL_INFILE=<span style="color: #000000;">ON </span>-DWITH_INNOBASE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_FEDERATED_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_BLACKHOLE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITHOUT_PARTITION_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_FAST_MUTEXES=1<span style="color: #000000;"> </span>-DWITH_ZLIB=<span style="color: #000000;">bundled </span>-DENABLED_LOCAL_INFILE=1<span style="color: #000000;"> </span>-DWITH_READLINE=1<span style="color: #000000;"> </span>-DWITH_EMBEDDED_SERVER=1<span style="color: #000000;"> </span>-DWITH_DEBUG=<span style="color: #000000;">0
- </span><span style="color: #008000;">#</span><span style="color: #008000;">-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32</span>
- <span style="color: #000000;">提示: 编译时可配置的选项很多,具体可参考结尾附录或官方文档:
- make
- </span><span style="color: #008000;">#</span><span style="color: #008000;">[100%] Built target my_safe_process</span>
- <span style="color: #000000;">make install
- ln </span>-s /application/mysql-5.5.32/ /application/<span style="color: #000000;">mysql
- 如果上述操作未出现错误,则MySQL5.</span>5<span style="color: #000000;">.32软件cmake方式的安装就算成功了。
- </span><span style="color: #008000;">#</span><span style="color: #008000;">拷贝配置文件</span>
- cp mysql-5.5.32/support-files/my-small.cnf /etc/<span style="color: #000000;">my.cnf
- </span><span style="color: #008000;">#</span><span style="color: #008000;">添加变量,并使之生效</span>
- echo <span style="color: #800000;">‘</span><span style="color: #800000;">export PATH=/application/mysql/bin:$PATH</span><span style="color: #800000;">‘</span> >>/etc/<span style="color: #000000;">profile
- source </span>/etc/<span style="color: #000000;">profile
- echo $PATH
- </span><span style="color: #008000;">#</span><span style="color: #008000;">授权用户及/tmp/临时文件目录</span>
- chown -R mysql.mysql /application/mysql/data/<span style="color: #000000;">
- chmod </span>-R 1777 /tmp/
- <span style="color: #008000;">#</span><span style="color: #008000;">初始化数据库</span>
- cd /application/mysql/scripts/<span style="color: #000000;">
- .</span>/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=<span style="color: #000000;">mysql
- cd ..</span>/
- <span style="color: #008000;">#</span><span style="color: #008000;">启动数据库</span>
- cp support-files/mysql.server /etc/init.d/<span style="color: #000000;">mysqld
- chmod </span>+x /etc/init.d/<span style="color: #000000;">mysqld
- </span>/etc/init.d/<span style="color: #000000;">mysqld start
- </span><span style="color: #008000;">#</span><span style="color: #008000;">检查端口</span>
- netstat -lntup|grep 3306
1、删除不必要的用户和库:
- <span style="color: #008000;">#</span><span style="color: #008000;">查看用户和主机列,从mysql.user里查看</span>
- select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除用户名为空的库,并检查</span>
- delete <span style="color: #0000ff;">from</span> mysql.user where user=<span style="color: #800000;">‘‘</span><span style="color: #000000;">;
- select user,host </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除主机名为localhost.localdomain的库,并检查</span>
- delete <span style="color: #0000ff;">from</span> mysql.user where host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost.localdomain</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- select user,host </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除主机名为::1的库,并检查。::1库的作用为IPV6</span>
- delete <span style="color: #0000ff;">from</span> mysql.user where host=<span style="color: #800000;">‘</span><span style="color: #800000;">::1</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除test库</span>
- drop database test;
2、添加额外管理员:
- <span style="color: #008000;">#</span><span style="color: #008000;"> 添加额外管理员,system作为管理员,oldsuo为密码</span>
- mysql> delete <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- Query OK, </span>2 rows affected (0.00<span style="color: #000000;"> sec)
- mysql</span>> grant all privileges on *.* to system@<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;"> with grant option;
- Query OK, 0 rows affected (</span>0.00<span style="color: #000000;"> sec)
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 刷新MySQL的系统权限相关表,使配置生效</span>
- mysql><span style="color: #000000;"> flush privileges;
- Query OK, 0 rows affected (</span>0.00<span style="color: #000000;"> sec)
- mysql</span>> select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- </span>+--------+-----------+
- | user | host |
- +--------+-----------+
- | system | localhost |
- +--------+-----------+
- 1 row <span style="color: #0000ff;">in</span> set (0.00<span style="color: #000000;"> sec)
- mysql</span>>
3、设置登录密码并开机自启:
- <span style="color: #008000;">#</span><span style="color: #008000;">设置密码,并登陆</span>
- /usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;">
- mysql </span>-usystem -<span style="color: #000000;">p
- </span><span style="color: #008000;">#</span><span style="color: #008000;">开机启动mysqld,并检查</span>
- <span style="color: #000000;">chkconfig mysqld on
- chkconfig </span>--list mysqld
mysql5.1.62安装编译
- <span style="color: #008000;">#</span><span style="color: #008000;">安装依赖包</span>
- yum –y install ncurses ncurses-devel gcc gcc-c++
- <span style="color: #008000;">#</span><span style="color: #008000;">添加mysql用户及组</span>
- <span style="color: #000000;">groupadd mysql
- useradd </span>-r -s /sbin/nologin -<span style="color: #000000;">g mysql mysql
- </span><span style="color: #008000;">#</span><span style="color: #008000;">mysql5.1.62编译参数:</span>
- ./<span style="color: #000000;">configure </span>--prefix=/usr/local/<span style="color: #000000;">mysql </span>--with-unix-soket-path=/usr/local/tmp/<span style="color: #000000;">mysql.sock </span>--localstatedir=/usr/local/mysql/<span style="color: #000000;">data </span>--enable-<span style="color: #000000;">assembler </span>--enable-thread-safe-<span style="color: #000000;">client </span>--with-mysqld-user=<span style="color: #000000;">mysql </span>--with-big-<span style="color: #000000;">tables </span>--without-<span style="color: #000000;">debug </span>--with-<span style="color: #000000;">pthread </span>--enable-<span style="color: #000000;">assembler </span>--with-extra-charsets=<span style="color: #000000;">complex </span>--with-<span style="color: #000000;">readline </span>--with-<span style="color: #000000;">ssl </span>--with-embedded-<span style="color: #000000;">server </span>--enable-local-<span style="color: #000000;">infile </span>--with-plugins=<span style="color: #000000;">partition,innobase </span>--with-mysqld-ldflags=-all-<span style="color: #000000;">static </span>--with-client-ldflags=-all-<span style="color: #000000;">static
- make </span>&&<span style="color: #000000;"> make install
- </span><span style="color: #008000;">#</span><span style="color: #008000;">初始化mysql</span>
- mkdir -p /usr/local/mysql/data <span style="color: #008000;">#</span><span style="color: #008000;">建立mysql数据文件目录</span>
- chown -R mysql.mysql /usr/local/mysql/ <span style="color: #008000;">#</span><span style="color: #008000;">授权mysql用户访问mysql安装目录</span>
- /usr/local/mysql/bin/mysql_install_db --user=mysql <span style="color: #008000;">#</span><span style="color: #008000;">初始化</span>
- <span style="color: #008000;">#</span><span style="color: #008000;">拷贝mysql启动脚本</span>
- cp support-files/my-small.cnf /etc/<span style="color: #000000;">my.cnf
- </span><span style="color: #008000;">#</span><span style="color: #008000;">cp support-files/mysql.server /etc/init.d/mysqld </span>
- chmod 700 /etc/init.d/<span style="color: #000000;">mysqld
- </span><span style="color: #008000;">#</span><span style="color: #008000;">配置mysql使用全局路径</span>
- echo <span style="color: #800000;">‘</span><span style="color: #800000;">export PATH=/application/mysql/bin:$PATH</span><span style="color: #800000;">‘</span> >>/etc/profile <span style="color: #008000;">#</span><span style="color: #008000;">添加变量到profile</span>
- source /etc/profile <span style="color: #008000;">#</span><span style="color: #008000;">使变量生效</span>
- echo $PATH <span style="color: #008000;">#</span><span style="color: #008000;">检查</span>
- <span style="color: #008000;">#</span><span style="color: #008000;">启动mysqld</span>
- /etc/init.d/<span style="color: #000000;">mysqld start
- </span><span style="color: #008000;">#</span><span style="color: #008000;">登陆报错,做软链接</span><span style="color: #008000;">
- #</span><span style="color: #008000;">ln -s /usr/local/mysql/bin/mysql /usr/bin/</span>
- <span style="color: #008000;">#</span><span style="color: #008000;">启动报错日志: Fatal error: Can‘t open and lock privilege tables: Table ‘mysql.host‘ doesn‘t #exist</span><span style="color: #008000;">
- #</span><span style="color: #008000;">解决方法: /usr/local/mysql/bin/mysql_install_db --user=mysql #初始化数据库即可</span>
- <span style="color: #008000;">#</span><span style="color: #008000;">登陆报错: mysql: unknown variable ‘datadir=/usr/local/mysql/data‘</span><span style="color: #008000;">
- #</span><span style="color: #008000;">解决方法: my.cnf 配置问题,vim /etc/my.cnf</span>
- <span style="color: #000000;">[client]
- </span><span style="color: #008000;">#</span><span style="color: #008000;">password = your_password</span>
- port = 3306<span style="color: #000000;">
- socket </span>= /tmp/<span style="color: #000000;">mysql.sock
- </span><span style="color: #008000;">#</span><span style="color: #008000;">datadir = /data1/mysql/var/ #这个不能加在上面,去掉</span>
- <span style="color: #000000;">
- [mysqld]
- port </span>= 3306<span style="color: #000000;">
- socket </span>= /tmp/<span style="color: #000000;">mysql.sock
- datadir </span>= /data1/mysql/var/ <span style="color: #008000;">#</span><span style="color: #008000;">加在这里就可以了</span>
- <span style="color: #008000;">#</span><span style="color: #008000;">设置mysql用户root 的密码为oldsuo</span>
- /usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span>
对于新手来说,字符集乱码问题无疑是头痛的问题,小编就带你不在头痛,从此幸福。
字符集,character set,就是一套表示字符的符号和这些的符号的底层编码;而校验规则,则是在字符集内用于比较字符的一套规则。简单的说,字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2,MySQL数据库字符集包括字符集和校对规则两个概念,字符集是定义数据库里面的内容字符串的存储方式,而校对规则是定义比较字符串的方式。
建议:中英文环境选择utf8
- <span style="color: #008000;">#</span><span style="color: #008000;"> 查看MySQL字符集设置情况</span>
- show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查看库的字符集</span>
- <span style="color: #000000;">show create database db;
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查看表的字符集</span>
- <span style="color: #000000;">show create table db_tb\G
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查询所有</span>
- <span style="color: #000000;">show collation;
- </span><span style="color: #008000;">#</span><span style="color: #008000;"> 设置表的字符集</span>
- set tables utf8;
View Code
- show create database nick_defailt\G <span style="color: #008000;">#</span><span style="color: #008000;">查看nick_defailt库字符集</span>
- mysql -uroot -p -e <span style="color: #800000;">"</span><span style="color: #800000;">SHOW CHARACTER SET;</span><span style="color: #800000;">"</span><span style="color: #000000;">
- show variables like </span><span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- mysql</span>> show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- </span>+-----------------------------------------+------------------------------------------------------------+
- | 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 <span style="color: #0000ff;">in</span> set (0.00<span style="color: #000000;"> sec)
- mysql</span>><span style="color: #000000;"> show create database nick_defailt \G
- </span>*************************** 1. row ***************************<span style="color: #000000;">
- Database: data
- Create Database: CREATE DATABASE `data` </span>/*!40100 DEFAULT CHARACTER SET utf8 */
- 1 row <span style="color: #0000ff;">in</span> set (0.00 sec)
- 1><span style="color: #000000;"> 系统方面
- cat </span>/etc/sysconfig/<span style="color: #000000;">i18n
- LANG</span>=<span style="color: #800000;">"</span><span style="color: #800000;">zh_CN.UTF-8</span><span style="color: #800000;">"</span>
- 2><span style="color: #000000;"> 客户端(程序),调整字符集为latin1。
- mysql</span>> set names latin1; <span style="color: #008000;">#</span><span style="color: #008000;">临时生效</span>
- Query OK, 0 rows affected (0.00<span style="color: #000000;"> sec)
- </span><span style="color: #008000;">#</span><span style="color: #008000;">更改my.cnf客户端模块的参数,实现set name latin1 的效果,并且永久生效。</span>
- <span style="color: #000000;"> [client]
- default</span>-character-set=<span style="color: #000000;">latin1
- </span><span style="color: #008000;">#</span><span style="color: #008000;">无需重启服务,退出登录就生效,相当于set name latin1。</span>
- 3><span style="color: #000000;"> 服务端,更改my.cnf参数
- [mysqld]
- default</span>-character-set=latin1 <span style="color: #008000;">#</span><span style="color: #008000;">适合5.1及以前版本</span>
- character-set-server=latin1 <span style="color: #008000;">#</span><span style="color: #008000;">适合5.5</span>
- 4><span style="color: #000000;"> 库、表、程序
- </span><span style="color: #008000;">#</span><span style="color: #008000;">建表指定utf8字符集</span>
- mysql><span style="color: #000000;"> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- Query OK, </span>1 row affected (0.00 sec)
- 1><span style="color: #000000;"> 导出表结构
- </span><span style="color: #008000;">#</span><span style="color: #008000;">以utf8格式导出</span>
- mysqldump -uroot -p --default-character-set=utf8 -d nick_defailt><span style="color: #000000;">alltable.sql
- </span>--default-character-set=gbk <span style="color: #008000;">#</span><span style="color: #008000;">表示已GBK字符集连接 –d 只表示表结构</span>
- 2><span style="color: #000000;"> 编辑alltable.sql 将utf8改成gbk。
- </span>3><span style="color: #000000;"> 确保数据库不在更新,导出所有数据
- mysqldump </span>-uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 nick_defailt><span style="color: #000000;">alldata.sql
- </span>4><span style="color: #000000;"> 打开alldata.sql将set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端)
- </span>5><span style="color: #000000;"> 建库
- create database oldsuo default charset gbk;
- </span>6><span style="color: #000000;"> 创建表,执行alltable.sql
- mysql </span>-uroot -p oldsuo <<span style="color: #000000;">alltable.sql
- </span>7><span style="color: #000000;"> 导入数据
- mysql </span>-uroot -p oldsuo <alltable.sql
MySQL最常用存储引擎Myisam和Innodb。mysql 5.5.5以后默认存储引擎为Innodb。
MySQL的每种引擎在MySQL里是通过插件的方式使用的,MySQL可以支持多种存储引擎。
建议:使用 Innodb引擎,因为支持回滚,后续博客会讲。
- 1<span style="color: #000000;">) MyISAM引擎系统库表对应文件
- [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> ll /data/3306/data/mysql/</span>
- -rw-rw----. 1 mysql mysql 10630 10月 31 16:05 user.frm <span style="color: #008000;">#</span><span style="color: #008000;">保存表的定义</span>
- -rw-rw----. 1 mysql mysql 1140 10月 31 18:40 user.MYD <span style="color: #008000;">#</span><span style="color: #008000;">数据文件</span>
- -rw-rw----. 1 mysql mysql 2048 10月 31 18:40 user.MYI <span style="color: #008000;">#</span><span style="color: #008000;">索引文件</span>
- <span style="color: #000000;">
- [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.frm </span>
- data/mysql/user.frm: MySQL table definition file Version 9<span style="color: #000000;">
- [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.MYD </span>
- data/mysql/user.MYD: DBase 3 data file (167514107<span style="color: #000000;"> records)
- [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.MYI</span>
- data/mysql/user.MYI: MySQL MISAM compressed data file Version 1
- 2<span style="color: #000000;">) InnoDB引擎
- [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> ll data/</span>
- -rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1
- <span style="color: #000000;">创建后引擎的修改
- 语法: ALTER TABLE student ENGINE </span>=<span style="color: #000000;"> INNODB;
- ALTER TABLE student ENGINE </span>= MyISAM;
修改实例
- mysql><span style="color: #000000;"> use teacher;
- Database changed
- mysql</span>><span style="color: #000000;"> show create table student;
- </span>+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | student |<span style="color: #000000;"> CREATE TABLE `student` (
- `id` int(</span>4<span style="color: #000000;">) NOT NULL AUTO_INCREMENT,
- `name` char(</span>20<span style="color: #000000;">) NOT NULL,
- `age` tinyint(</span>2) NOT NULL DEFAULT <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- `dept` varchar(</span>16<span style="color: #000000;">) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `index_name` (`name`)
- ) ENGINE</span>=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
- +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row <span style="color: #0000ff;">in</span> set (0.01<span style="color: #000000;"> sec)
- mysql</span>> ALTER TABLE student ENGINE =<span style="color: #000000;"> MyISAM;
- Query OK, </span>3 rows affected (0.05<span style="color: #000000;"> sec)
- Records: </span>3<span style="color: #000000;"> Duplicates: 0 Warnings: 0
- mysql</span>><span style="color: #000000;"> show create table student;
- </span>+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | student |<span style="color: #000000;"> CREATE TABLE `student` (
- `id` int(</span>4<span style="color: #000000;">) NOT NULL AUTO_INCREMENT,
- `name` char(</span>20<span style="color: #000000;">) NOT NULL,
- `age` tinyint(</span>2) NOT NULL DEFAULT <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- `dept` varchar(</span>16<span style="color: #000000;">) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `index_name` (`name`)
- ) ENGINE</span>=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
- +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row <span style="color: #0000ff;">in</span> set (0.00 sec)
- mysql><span style="color: #000000;"> create table mess (
- </span>-> id int(4) <span style="color: #0000ff;">not</span><span style="color: #000000;"> null,
- </span>-> name char(20) <span style="color: #0000ff;">not</span><span style="color: #000000;"> null,
- </span>-> age tinyint(2) NOT NULL default <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- </span>-> dept varchar(16<span style="color: #000000;">) default NULL
- </span>-> ) ENGINE=MyISAM CHARSET=<span style="color: #000000;">utf8;
- Query OK, 0 rows affected (</span>0.00 sec)
运行相关:
- 1<span style="color: #000000;">、 单实例mysql启动
- [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld start</span>
- <span style="color: #000000;">Starting MySQL [确定]
- </span><span style="color: #008000;">#</span><span style="color: #008000;">mysqld_safe –user=mysql &</span>
- 2<span style="color: #000000;">、 查看MySQL端口
- [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> ss -lntup|grep 3306</span>
- tcp LISTEN 0 50 *:3306 *:* users:((<span style="color: #800000;">"</span><span style="color: #800000;">mysqld</span><span style="color: #800000;">"</span>,19651,10<span style="color: #000000;">))
- </span>3<span style="color: #000000;">、 查看MySQL进程
- [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> ps -ef|grep mysql|grep -v grep</span>
- 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/<span style="color: #000000;">localhost.localdomain.pid
- mysql </span>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<span style="color: #000000;">、 MySQL启动原理
- </span>/etc/init.d/<span style="color: #000000;">mysqld 是一个shell启动脚本,启动后最终会调用mysqld_safe脚本,最后调用mysqld服务启动mysql。
- </span><span style="color: #800000;">"</span><span style="color: #800000;">$manager</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--mysqld-safe-<span style="color: #000000;">compatible </span>--user=<span style="color: #800000;">"</span><span style="color: #800000;">$user</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--pid-file=<span style="color: #800000;">"</span><span style="color: #800000;">$pid_file</span><span style="color: #800000;">"</span> >/dev/null 2>&1 &
- 5<span style="color: #000000;">、关闭数据库
- [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld stop</span>
- <span style="color: #000000;">Shutting down MySQL.... [确定]
- </span>6<span style="color: #000000;">、 查看mysql数据库里操作命令历史
- cat </span>/root/<span style="color: #000000;">.mysql_history
- </span>7<span style="color: #000000;">、 强制linux不记录敏感历史命令
- HISTCONTROL</span>=<span style="color: #000000;">ignorespace
- </span>8<span style="color: #000000;">、 mysql设置密码
- </span>/usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span>
- 9<span style="color: #000000;">、 mysql修改密码,与多实例指定sock修改密码
- mysqladmin </span>-uroot -passwd password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;">
- mysqladmin </span>-uroot -passwd password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span> -S /data/3306/mysql.sock
操作相关:
- <span style="color: #008000;">#</span><span style="color: #008000;">登陆mysql数据库</span>
- mysql -<span style="color: #000000;">uroot –p
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看有哪些库</span>
- <span style="color: #000000;">show databases;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除test库</span>
- <span style="color: #000000;">drop database test;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">使用test库</span>
- <span style="color: #000000;">use test;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看有哪些表</span>
- <span style="color: #000000;">show tables;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看suoning表的所有内容</span>
- select * <span style="color: #0000ff;">from</span><span style="color: #000000;"> suoning;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看当前版本</span>
- <span style="color: #000000;">select version();
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看当前用户</span>
- <span style="color: #000000;">select user();
- </span><span style="color: #008000;">#</span><span style="color: #008000;">查看用户和主机列,从mysql.user里查看</span>
- select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">删除前为空,后为localhost的库</span>
- drop user <span style="color: #800000;">""</span><span style="color: #000000;">@localhost;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">刷新权限</span>
- <span style="color: #000000;">flush privileges;
- </span><span style="color: #008000;">#</span><span style="color: #008000;">跳出数据库执行命令</span>
- system ls;
忘记mysql登录密码也是一件头疼的事,那么小编会让你继续幸福。
- 1> 普通方式
- <span style="color: #008000;">#</span><span style="color: #008000;">> service mysqld stop</span><span style="color: #008000;">
- #</span><span style="color: #008000;">>mysqld_safe --skip-grant-tables &</span>
- 输入 mysql -uroot -<span style="color: #000000;">p 回车进入
- </span>><span style="color: #000000;">use mysql;
- </span>> update user set password=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span><span style="color: #000000;">;
- 更改密码为 newpassord
- </span>><span style="color: #000000;"> flush privileges; 更新权限
- </span>><span style="color: #000000;"> quit 退出
- service mysqld restart
- mysql </span>-uroot -<span style="color: #000000;">p新密码进入<br>
- </span>2> 普通方式的简写<span style="color: #000000;">
- service mysqld stop
- mysqld_safe </span>--skip-grant-tables --user=mysql &<span style="color: #000000;">
- mysql
- update mysql.user set password</span>=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span> <span style="color: #0000ff;">and</span> host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- flush privileges;
- mysqladmin </span>-uroot -<span style="color: #000000;">pnewpass shutdown
- </span>/etc/init.d/<span style="color: #000000;">mysqld start
- mysql </span>-uroot -pnewpass <span style="color: #008000;">#</span><span style="color: #008000;">登陆</span>
- 3><span style="color: #000000;">多实例方式
- killall mysqld
- mysqld_safe –defaults</span>-file=/data/3306/my.cnf –skip-grant-table &<span style="color: #000000;">
- mysql –u root –p –S </span>/data/3306/mysql.sock <span style="color: #008000;">#</span><span style="color: #008000;">指定sock登陆</span>
- update mysql.user set password=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span><span style="color: #000000;">;
- flush privileges;
- mysqladmin </span>-uroot -<span style="color: #000000;">pnewpass shutdown
- </span>/etc/init.d/<span style="color: #000000;">mysqld start
- mysql </span>-uroot -pnewpass <span style="color: #008000;">#</span><span style="color: #008000;">登陆</span>
注:本文有看不懂的在后续博客有详解
MySQL 系列(一) 生产标准线上环境安装配置案例及棘手问题解决
标签:display span 文件目录 example primary 100% ibdata1 src prim