CentOS 7 yum安装MariaDB

CentOS 7 中yum安装MariaDB

官方安装说明地址:https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/

1. 设置仓库

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

2.执行安装

yum install MariaDB-server

3.启动服务

systemctl start mysql.service

4.服务随系统启动

systemctl enable mysql.service

5.取消随系统启动

systemctl disable mysql.service

6.修改root密码和远程访问权限

命令行输入mysql,进到数据库命令提示行,use mysql,
grant all privileges on *.* to root@"%" identified by "mima";  
flush privileges;

'all privileges ':所有权限 也可以写成 select ,update等。
. 所有库的所有表 如 databasename.*。
% 数据库所在的IP, %表示所有。
identified by ‘密码’ 表示通过密码连接

##MariaDB字符集编码

查看数据库支持的所有字符集
SHOW CHARACTER SET;
SHOW COLLATION;

一.查看编码

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

默认是latin1,外部访问数据乱码的问题就出在这个connection连接层上

二.设置编码
命令行修改

  1. SET NAMES ‘utf8’;
    它相当于下面的三句指令:
    SET character_set_client = utf8;
    SET character_set_results = utf8;
    SET character_set_connection = utf8;
    一般只有在访问之前执行这个代码就解决问题了

  2. 创建数据库是指定编码
    CREATE DATABASE database_name CHARACTER SET utf8;

MariaDB [(none)]> CREATE DATABASE foo CHARACTER SET utf8;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE foo;

Database changed

MariaDB [foo]> SHOW VARIABLES LIKE ‘character%’;

±-------------------------±---------------------------+

| 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 | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

±-------------------------±---------------------------+

8 rows in set (0.00 sec)

MariaDB [foo]> SHOW VARIABLES LIKE ‘collation%’;

±---------------------±------------------+

| Variable_name | Value |

±---------------------±------------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | latin1_swedish_ci |

±---------------------±------------------+

3 rows in set (0.00 sec)

注意:虽然在建库的时候有指定字符集,但character_set_server 仍然是latin1, 需要修改配置文件/etc/my.cnf

  1. 创建表指定编码
    CREATE TABLE type (
    id int(10) unsigned NOT NULL auto_increment,
    flag_deleted enum(‘Y’,‘N’) character set utf8 NOT NULL default ‘N’,
    flag_type int(5) NOT NULL default ‘0’,
    type_name varchar(50) character set utf8 NOT NULL default ‘’,
    PRIMARY KEY (id)
    ) DEFAULT CHARSET=utf8;
    示例:

CREATE TABLE mytable

(

columnn1 INT,

columnn2 VARCHAR(10)

)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

CREATE OR REPLACE TABLE mytable

(columnn1 INT,

columnn2 VARCHAR(10),

column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci

)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;

还可以限制某个字段的字符集作为筛选条件

  1. 修改数据库成utf8
    ALTER DATABASE database_name CHARACTER SET utf8;

  2. 修改表默认用utf8
    ALTER TABLE table_name CHARACTER SET utf8;

  3. 修改字段用utf8
    MariaDB [foo]> DESC hello;

±------±---------±-----±----±--------±------+

| Field | Type | Null | Key | Default | Extra |

±------±---------±-----±----±--------±------+

| id | int(10) | YES | | NULL | |

| name | char(50) | YES | | NULL | |

±------±---------±-----±----±--------±------+

2 rows in set (0.00 sec)

MariaDB [foo]> ALTER TABLE hello MODIFY name char(50) CHARACTER SET utf8;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

配置文件
Linux下一般是 /etc/my.cnf,Windows下一般在系统目录下或者在MySQL的安装目录下名字叫my.ini

[mysqld]
character_set_server = utf8
lower_case_table_names = 1 //表名不区分大小写(此与编码无关)

[mysqld_safe]
character_set_server = utf8

[mysql]
default-character-set = utf8

[mysql.server]
default-character-set = utf8

[client]
default-character-set = utf8

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.41-MariaDB MariaDB Server

Copyright © 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE ‘collation%’;

±---------------------±----------------+

| Variable_name | Value |

±---------------------±----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

±---------------------±----------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE ‘character%’;

±-------------------------±---------------------------+

| 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/share/mysql/charsets/ |

±-------------------------±---------------------------+

8 rows in set (0.00 sec)

注意:新版本的mariadb,如mariadb-10.1的配置些许有些变化
[mysqld]
init_connect = ‘SET collation_connection = utf8_general_ci’

init_connect = ‘SET NAMES utf8’

character_set_server = utf8

collation_server = utf8_general_ci

[mysqld_safe]
init_connect = ‘SET collation_connection = utf8_general_ci’

init_connect = ‘SET NAMES utf8’

character_set_server = utf8

collation_server = utf8_general_ci

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值