1.一つのカラムにUNIQUE制約を追加
CREATE TABLE Member_tbl
(
User_ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (User_ID)
)
2,複数のカラムにUNIQUE制約を追加
CREATE TABLE Member_tbl
(
User_ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (User_ID,LastName)
)
3.UNIQUE 制約を削除
ALTER TABLE Member_tbl
DROP INDEX uc_PersonID
1.ユーザ作成
CREATE USER ‘testuser’@’localhost’ IDENTIFIED BY ‘667788’;
CREATE USER ‘testuser2’@’172.12.2.100_’ IDENDIFIED BY ‘667788’;
CREATE USER ‘testuser2’@’%’ IDENTIFIED BY ‘667788’;
CREATE USER ‘testuser2’@’%’ IDENTIFIED BY ”;
CREATE USER ‘testuser2’@’%’;
2.権限の設定
GRANT SELECT, INSERT ON test.user TO ‘testuser2’@’%’;
GRANT ALL ON *.* TO ‘testuser2’@’%’;
3.ユーザーのパスワードの設定
SET PASSWORD FOR ‘testuser2’@’%’ = PASSWORD(“667788”);
4.権限の削除
REVOKE SELECT ON *.* FROM ‘testuser2’@’%’;
5.ユーザーの削除
DROP USER ‘username’@’host’;
設定内容
[mysqld]
character-set-server=utf8
bind-address = 0.0.0.0
port = 3306
basedir=C:/arkgametest/mysql-5.7.19-winx64
datadir=C:/arkgametest/mysql-5.7.19-winx64/data
max_connections=200
# skip_grant_tables
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[client]
default-character-set=utf8
1.データベース権限の追加
GRANT 権限 ON dbname.* TO ‘username’@’hostname’;
例
GRANT SELECT, INSERT ON USERINFO_SSDB.* TO ‘system_manager’@’172.17.2.100’;
2.テーブル権限の追加
GRANT 権限 ON dbname.テーブル名 TO ‘username’@’hostname’;
例
GRANT SELECT, INSERT ON USERINFO_SSDB.STUDENT_TBL TO ‘system_manager’@’172.17.2.100’;
3.カラム権限の追加
GRANT 権限 (column_name) ON dbname.テーブル名 TO ‘username’@’hostname’;
例
GRANT SELECT (COL1), INSERT (COL1,COL2) ON USERINFO_SSDB.STUDENT_TBL TO ‘system_manager’@’172.17.2.100’;
4.ユーザーの権限の追加
SHOW GRANTS FOR ‘username’@’hostname’;
例
SHOW GRANTS FOR ‘system_manager’@’172.17.2.100’;
5.権限の削除
REVOKE 権限 ON 権限レベル FROM ‘username’@’hostname’;
例
REVOKE select ON USERINFO_SSDB.STUDENT_TBL FROM ‘system_manager’@’172.17.2.100’;
1.3306のポートを開放
#firewall-cmd –permanent –zone public –add-port 3306/tcp
2.MySQLサービスをゾーンに追加
# firewall-cmd –permanent –zone public –add-service mysql
success
# firewall-cmd –reload
success
3.ファイアウォールに設定を反映
# firewall-cmd –info-zone public
public (active)
target: default
icmp-block-inversion: no
interfaces: eth0
sources:
services: dhcpv6-client http mysql ssh
ports: 3306/tcp 80/tcp
protocols:
masquerade: no
forward-ports:
sourceports:
icmp-blocks:
rich rules:
4.設定変更
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@”xxx.xxx.xxx.xxx” IDENTIFIED BY ‘ygl989195’ WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO root@”xxx.xxx.xxx.xxx” IDENTIFIED BY ‘ygl989195’ WITH GRANT OPTION;
MariaDB [(none)]> flush PRIVILEGES;
5.ポート確認
MariaDB [(none)]> SHOW VARIABLES LIKE ‘port’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| port | 3306 |
+—————+——-+
1.mySQL5.7のリポジトリを追加
#rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
#cd /etc/yum.repos.d
#ll
以下のリポジトリを確認
mysql-community-source.repo
mysql-community.repo
2.MySQLをインストール
# yum -y install mysql-community-server
3.mysqlのバージョンを確認
# mysql –version
パッケージの確認
# rpm -qa | grep mysql
mysqlをインストール
$ sudo apt-get update
$ apt-cache show mysql-server | grep Version
$ sudo apt-get install mysql-server
$ mysql –version
設定ファイル
$ cd /etc/mysql
$ cp my.cnf my.cnf.org
$ sudo vi my.cnf
追記
[client]
default-character-set = utf8
[mysqld]
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8
mysqlを再起動
$ sudo service mysql restart
# yum install mariadb-server -y
systemctl stop mariadb.service
# systemctl start mariadb.service
# systemctl enable mariadb.service
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>