安装环境
[root@node3 ~]# yum install cmake -y
[root@node3 ~]# !268
yum groupinstall "Development Tools" "Server Platform Development" -y
创建用户
[root@node3 ~]# groupadd -r -g 306 mysql
[root@node3 ~]# useradd -r -u 306 -g 306 mysql
[root@node3 ~]# cat /etc/passwd|grep 306
mysql:x:306:306::/home/mysql:/bin/bash
创建目录
[root@node3 ~]# mkdir -pv /mydata/data
[root@node3 ~]# chown -R mysql.mysql /mydata
安装mariradb
[root@node3 ~]# ls
anaconda-ks.cfg CentOS-fasttrack.repo epel-release-latest-6.noarch.rpm install.log 公共的 图片 音乐
CentOS-Base.repo CentOS-Media.repo epel.repo install.log.syslog 模板 文档 桌面
CentOS-Debuginfo.repo CentOS-Vault.repo epel-testing.repo mariadb-10.0.13.tar.gz 视频 下载
[root@node3 ~]# tar xf mariadb-10.0.13.tar.gz
cmake编译指令介绍:
指定安装文件的安装路径时常用的选项:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/mydata/data
-DSYSCONFDIR=/etc
默认编译的存储引擎包括:csv、myisam、myisammrg和heap。若要安装其它存储引擎,可以使用类似如下编译选项:
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
若要明确指定不编译某存储引擎,可以使用类似如下的选项:
-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
比如:
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1
如若要编译进其它功能,如SSL等,则可使用类似如下选项来实现编译时使用某库或不使用某库:
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
其它常用的选项:
-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0
-DENABLE_PROFILING=1
如果想清理此前的编译所生成的文件,则需要使用如下命令:
#make clean
#rm CMakeCache.txt
编译安装MariaDB:
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/mydata/data \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
备注:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql //安装目录
-DINSTALL_DATADIR=/mydata/data //数据库存放目录
-DDEFAULT_CHARSET=utf8 //使用utf8字符
-DDEFAULT_COLLATION=utf8_general_ci //校验字符
-DEXTRA_CHARSETS=all //安装所有扩展字符集
-DENABLED_LOCAL_INFILE=1 //允许从本地导入数据
注意事项:
重新编译时,需要清除旧的对象文件和缓存信息。
#make clean
#rm -f CMakeCache.txt
#rm -rf /etc/my.cnf
错误:Curses library not found. Please install appropriate package,
解决方案:
先安装 ncurses-devel 包
yum install ncurses-devel
再删除刚才编译生成的 CMakeCache.txt 文件
rm CMakeCache.txt
再次执行一次cmake ...
一般都可以顺利安装的。
[root@node3 mariadb-10.0.13]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data/
[root@node3 mysql]# make
[root@node3 mysql]# make install
[root@node3 mariadb-10.0.13]# cd /usr/local/mysql/
[root@node3 mysql]# ll
总用量 212
drwxr-xr-x. 2 root root 4096 2月 18 11:33 bin
-rw-r--r--. 1 root root 17987 8月 9 2014 COPYING
-rw-r--r--. 1 root root 26545 8月 9 2014 COPYING.LESSER
-rw-r--r--. 1 root root 1553 8月 9 2014 CREDITS
drwxr-xr-x. 3 root root 4096 2月 18 11:33 data
drwxr-xr-x. 2 root root 4096 2月 18 11:33 docs
-rw-r--r--. 1 root root 8245 8月 9 2014 EXCEPTIONS-CLIENT
drwxr-xr-x. 3 root root 4096 2月 18 11:33 include
-rw-r--r--. 1 root root 8694 8月 9 2014 INSTALL-BINARY
drwxr-xr-x. 3 root root 4096 2月 18 11:33 lib
drwxr-xr-x. 4 root root 4096 2月 18 11:34 man
drwxr-xr-x. 11 root root 4096 2月 18 11:34 mysql-test
-rw-r--r--. 1 root root 90897 8月 9 2014 README
drwxr-xr-x. 2 root root 4096 2月 18 11:33 scripts
drwxr-xr-x. 27 root root 4096 2月 18 11:33 share
drwxr-xr-x. 4 root root 4096 2月 18 11:34 sql-bench
drwxr-xr-x. 3 root root 4096 2月 18 11:33 support-files
[root@node3 mysql]# cp support-files/my-large.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? yes
[root@node3 mysql]# vim /etc/my.cnf
thread_concurrency = 8
datadir = /mydata/data
skip_name_relsove = ON
innodb_file_per_table = ON
复制服务
[root@node3 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@node3 mysql]# service mysqld start
Starting MySQL.... [确定]
安装程序
[root@node3 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
[root@node3 mysql]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@node3 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.5-10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2013, 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>
头文件
[root@node3 mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
"/usr/include/mysql" -> "/usr/local/mysql/include/"
库文件
[root@node3 mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
man文件
[root@node3 mysql]# vim /etc/man.config
MANPATH /usr/local/mysql/man
创建个隐藏文件在root家目录:
[root@tom2 ~]# vim .my.cnf
[mysql]
user = root
password = oracle
host = 127.0.0.1
这样我们就能实现mysql不输入用户名密码直接登录数据库了,方便操作。
————————————————————————————————————
mysql
(1)查看存储引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.09 sec)
(2)root密码修修改
1. set password=pasword('');
2. update mysql.user set password=password('') where user="root"and host="localhost"
flush privileges;
3. #mysqladmin -uroot pssword "mima"
mysqladmin -uroot -p"mima"
(3)删除空用户
select host,user,password from mysql.user where password='';
drop user ''@'localhost';
注意: 以上两个可以使用mysql_secure_installation
a)为root用户设置密码
b)删除匿名账号
c)取消root用户远程登录
d)删除test库和对test库的访问权限
e)刷新授权表使修改生效
(4)跳过主机名反解
vim /etc/my.cnf
skip_name_resovle = NO
innodb_file_per_table = NO
datadir = /mydata.data
(5)如何获取程序默认使用的配置:
mysql --print-defaults
mysqld --print-defaults
[root@node3 ~]# mysql --print-defaults
mysql would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
[root@node3 ~]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --skip-external-locking --key_buffer_size=256M --max_allowed_packet=1M --table_open_cache=256 --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 --datadir=/mydata/data --skip_name_resolve=ON --innodb_file_per_table=ON --log-bin=mysql-bin --binlog_format=mixed --server-id=1
(7)客户端类应用程序的可用选项:
-u, --user=
-h, --host=
-p, --passowrd=
-P, --port=
--protocol=
-S, --socket=
-D, --database=
-C, --compress
当不需要联入数据库时;
[root@node3 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
(8)mysql的使用模式:
交互式模式:
可运行命令有两类:
客户端命令:
\h, help或者\G
服务器端命令:
SQL, 需要语句结束符;
脚本模式:
1. # mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql
2. mysql> source /path/from/somefile.sql---是否有权限使用
(9)服务器端(mysqld):工作特性有多种定义方式
命令行选项
配置文件参数
获取可用参数列表:
mysqld --help --verbose
[root@node3 mysql]# /usr/local/mysql/bin/mysqld --help --verbose|less
/usr/local/mysql/bin/mysqld Ver 10.0.13-MariaDB-log for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Starts the MariaDB database server.
Usage: /usr/local/mysql/bin/mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-10.0 mariadb mariadb-10.0 client-server
The following options may be given as the first a()rgument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
(10)获取运行中的mysql进程使用各服务器参数及其值:
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
注意:其中有些参数支持运行时修改,会立即生效;有些参数不支持,且只能通过修改配置文件,并重启服务器程序生效;
有些参数作用域是全局的,且不可改变;有些可以为每个用户提供单独的设置;
(11)修改服务器变量的值:
mysql> help SET
全局:
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
会话:
mysql> SET [SESSION] system_var_name=value;
(12)状态变量:用于保存mysqld运行中的统计数据的变量;
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;
MariaDB [(none)]> show global status;
+-----------------------------------------------+------------------+
| Variable_name | Value |
+-----------------------------------------------+------------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Access_denied_errors | 0 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15736 |
| Aria_pagecache_blocks_used | 2 |
| Aria_pagecache_read_requests | 44 |
| Aria_pagecache_reads | 1 |
| Aria_pagecache_write_requests | 2 |
| Aria_pagecache_writes | 0 |
| Aria_transaction_log_syncs | 0 |
(13)MySQL 数据类型:
字符型
数值型
日期时间型
内建类型
字符型:
CHAR(255字符), BINARY:定长数据类型;(区分大小写)
VARCHAR(65535字符), VARBINARY:变长数据类型;需要结束符(1/2);(区分大小写)
TEXT:TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT(2^8;2^16;2^32;2^64)(不区分大小写)
BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB(2^8;2^16;2^32;2^64)(区分大小写)
ENUM(固定选择), SET(内建字符)
附enum
数值型:
精确数值型:
整型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT(2^8;2^16;2^32;2^64)
十进制型:DECIMAL(g,f)(精确)
近似数值型
浮点型:
FLOAT(g,f)
DOUBLE(g,f)
日期时间型:
DATE(3个字节)
TIME(3个字节)
DATETIME(8个字节)
TIMESTAMP
YEAR(2), YEAR(4)
字符类型修饰符:跟在类型后面
NOT NULL: 非空约束;
NULL:默认
DEFAULT 'STRING': 指明默认值;
CHARACTER SET '':使用的字符集;(从数据库继承)
COLLATION:使用的排序规则(从数据库继承)
mysql> SHOW CHARACTER SET;(查看,选择)
mysql> SHOW COLLATION;(查看,选择)
整型数据修饰型:
NOT NULL
NULL
DEFAULT NUMBER
AUTO_INCREMENT:
UNSIGNED--无符号
PRIMARY KEY|UNIQUE KEY
NOT NULL(UNIQUE KEY)
mysql> SELECT LAST_INSERT_ID();-----上次自动到多少
日期时间型修饰符:
NOT NULL
NULL
DEFAULT
内建类型SET和ENUM的修饰符:
NOT NULL
NULL
DEFAULT
(14)SQL MODE:定义mysqld对约束等的响应行为;
修改方式:
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
需要修改权限;仅对修改后新创建的会话有效;对已经建立的会话无效;
mysql> SET SESSION sql_mode='MODE';(对话,现有对话有效)
mysql> SET @@session.sql_mode='MODE';
常用MODE:TRADITIONAL(不许对非法值插入), STRICT_TRANS_TABLES(对支持事务类型的表做严格约束), or STRICT_ALL_TABLES(对所有表做严格约束)
MariaDB [(none)]> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
实例:
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> create table t1 (
-> id int unsigned auto_increment primary key not null,
-> name char(5) not null);
Query OK, 0 rows affected (0.16 sec)
MariaDB [mydb]> insert into t1 (name) values ('tom'),('blackberry');
Query OK, 2 rows affected, 1 warning (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 1
MariaDB [mydb]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 2 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [mydb]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | black |
+----+-------+
2 rows in set (0.00 sec)
实例2
MariaDB [mydb]> set sql_mode="traditional";-------只对当前会话有效
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb]> insert into t1 (name) values ('redberry');
ERROR 1406 (22001): Data too long for column 'name' at row 1
使用其他会话联入查看
MariaDB [(none)]> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
(15)SQL: DDL, DML
DDL: 数据定义语言;
CREATE, ALTER, DROP
DB组件:数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器等
CREATE相关的常用命令:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DML:数据操作语言;
INSERT, DELETE, UPDATE, SELECT
创建表:CREATE TABLE
(1) 直接创建;
(2) 通过查询现存的表创建;新表会被直接插入查询而来的数据;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
(3) 通过复制现存的表的表结构创建;不复制数据;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
注意:Storage Engine是指表类型,也即在表创建时指明其使用的存储引擎;
同一个库中表要使用同一种存储引擎类型;
查看默认引擎
MariaDB [mydb]> show global variables like '%def%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_regex_flags | |
| default_storage_engine | InnoDB |
| default_week_format | 0 |
| table_definition_cache | 400 |
+------------------------+--------+
(16)查看表结构:
DESCRIBE tbl_name;
(17)查看表状态信息:
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
MariaDB [mydb]> show table status like "t1"\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2016-02-19 00:32:01
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)