mariadb docker容器集群搭建 mariadb-client

一、安装MariaDB及其配置步骤   

1)创建存放数据目录及其用户
mkdir -pv /mydata/data
groupadd -r mysql   
useradd -g mysql -r mysql   
2)授权数据存放目录
chown -R /mydata/data  
3)解压mariadb-5.5.48-linux-x86_64.tar.gz 
tar xfmariadb-5.5.48-linux-x86_64.tar.gz -C /usr/loca/mysql    
4)为数据库提供配置文件及其启动脚本 
cd /usr/local/mysql   
cp support-files/my-large.cnf/etc/my.cnf 
cp support-files/mysql.server/etc/init.d/mysqld  
chmod +x /etc/init.d/mysqld  
chkconfig --add /etc/init.d/mysqld 
chkconfig mysqld on    
5)初始化数据库 
cd /usr/local/mysql 
./configure --user=mysql--datadir=/mydata/data   
6)加入环境变量  
echo "exportPATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh  
source /etc/profile.d/mysql.sh  
7)启动数据库  
/etc/init.d/mysqld start 或service mysqld start  
 
二、服务端命令
1.DDL(数据定义语言)，分别主要用于管理数据库组件，例如索引，视图，用户，存储过程：create alter drop等。
DDL命令详解：create,alter,drop   
创建：create 
    create{DATABASES|SCHEMA} [IF NOT EXISTS] db_name [create_spacification]....
    例如：create database if notexists testdb; 
    获取帮助：help 或 help command 
    查看所有的数据库：showdatabases; 
    查看支持的字符集：show character set;
    查看支持的所有排序的规则：show collation;
修改：alter 
    ALTER{DATABASE|SCHEMA} [db_name] alter_specification ...    
删除：drop  
    DROP{DATABASE|SCHEMA} [db_name]  例如：drop database testdb;   
 
表操作：
创建表
    create[temporary] talbe [if not exists] tbl_name (create_definition,....) 
    例如：create table if notexists students(id int unsigned not null,name varchar(20),age tinyintunsigned,gender enum('f','m'),primary key(id));
    查看表结构：desc students; 
    查看建表过程：show create tablestudents\G  
    查看表的状态信息：show table statuslike 'students'; show table status like '%stu%';
    查看表数据库支持的存储引擎：showengines; 
修改表： 
    ALTER[ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_spcification]  
字段:
    添加字段：ADD [column] col_namecolumn_definition  
    alter tablestudents add number int(13) not null; 
    alter tablestudents add number int(13) not null after age;  
    删除：DROP [COLNUM]col_name 
    alter tablestudents drop number; 
    修改：CHANGE [COLNUM]old_col_name new_col_name column_definition   
             MODIFY  [COLNUM] old_col_name new_col_name column_definition  
    alter tablestudents modify name varchar(30) after age;  
添加键：
    add {primarykey|unique key|foreign key}(col1,col2....)  
    alter tablestudents add primary key(number,id);
删除键：
    drop primarykey  
    alter tablestudents drop primary key 
索引：
    添加：add {index|key}{index_name} 
    删除：drop {index|key}{index_name}  
    alter table addindex age (age); 
    查看表上的索引信息：show indexes fromtbl_name;     
创建：
    CREATE[ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name(index_col_name,...)
    [index_option]...
    create indexname_and_class on students (name,age);
删除：
    drop indexname_and_class on students;

            

图示：

 

2.DML(数据操纵语言)：主要用于管理表中的数据，实现数据的增删改查(insert,delete,update,select) 

inset into:
    insert into:insert intotable_name [cols.......] value (val1,val2,val3....)
    例如：insert into studentsvalues (1,'alren',25,'m','one',169); 
    insert into students (id,name) values(1,'alren'),(2,'chen');
    注意：在使用字符型数据时需使用引号，数值则不需使用引号，使用也行。
delete:
    delete from students where age is null; 
    delete from studnets order by age desc limit 20;  
update  
    update students set age=age+15 where name like'%ren%';        
select:
    select * fromtbl_name; 
    selectcol1,col2,col3,....from tbl_name;    
    select id,namefrom students;
    selectcol1,col2 ... from tbl_name where clause; 
    select name,agefrom students where age >11 and age <30;
    select name,agefrom students where age between 11 and 30; 
    select name,agefrom students where age is not null;  
    select id,namefrom students order by id desc; #desc升序
    selectid,name,age from students order by id asc;  #asc将序
 
删除用户账号：
    drop user 'user'@'localhost';   
    dorp user 'testuser'@'192.168.%.%';  
授权： 
    grant privil_type on [object_type] db_name to'user'@'host' indentified by 'password'
privileges_type:
    *.*:所有的库中的所有的表 
    db_name.*指定库中的所有表
    db_name.tbl_name:指定用户上的特定表 
    db_name>routine_name:指定库上的存储过程或函数   
    实例：grant selct,updateon mydb.students to 'testuser'@'192.168.%.%' identified by 'paswd'
              grant all *.* to'root'@'localhost' identified by 'password'

https://blog.51cto.com/purify/1858677