Category Archives: MySQL

「CentOS7入門」MySQLにクライアントでリモート接続するの設定方法

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 |
+—————+——-+

CentOS6にMySQL5.7をインストールするメモ

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

Ubuntuに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

CentOS7にMySQLを再起動するメモ

# 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)]>

「MySQL入門」str_to_date()で文字列からdatetime型にするコマンド

サンプル:
SELECT
str_to_date(
concat (left(date_str,10) , right(date_str,10)),
‘%Y/%m/%d %H:%i:%s’ ) as cft FROM xxx;

MySQLにユーザー作成、権限設定のメモ

操作手順
#create user ‘testuser’@’%’ identified by ‘12345’;
#grant all privileges on *.* to ‘testuser’;
#commit;
#flush privileges;

CentOS7でMySQL完全アンインストールする

操作手順
#yum remove mysql mysql-server mysql-devel mysql-libs compat-mysql51 mysql-community-client mysql-community-libs mysql-community-server mysql-community-common mysql-community-release.noarch
#rm -rf /var/lib/mysql
#rm /etc/my.cnf
#rm -rf /var/lib/mysql/
#rm -rf /usr/lib64/mysql
#rm -rf /usr/lib/mysql
#rm -rf /usr/share/mysql
#rm –rf /usr/my.cnf
#rm -rf /root/.mysql_sercret
#chkconfig –del mysqld

#yum list installed mysql*

「MySQL入門」データベース、テーブルの権限を設定するコマンド

1.グローバル
mysql>grant select,insert,update,delete   on *.* to testuser;

2.データベース
mysql>grant select,insert,update,delete on testdb.* to testuser;

3.テーブル
mysql>grant select,insert,update,delete on testdb.test_table to testuser;

4.カラム
mysql>grant 権限 (column1, column2, …) on testdb.test_table to testuser;

「MySQL」ストアド(PROCEDURE)、カーソル(CURSOR)を使用するサンプルコード

サンプルコード:
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cftbl`()
COMMENT ‘insert table’
BEGIN
DECLARE root INT;
DECLARE zid INT;
DECLARE done INT DEFAULT FALSE;
DECLARE rs CURSOR FOR select id,tid from demo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN rs;
read_loop: LOOP
FETCH  NEXT from rs INTO root,zid;
IF done THEN
LEAVE read_loop;
END IF;
insert into changfatbl(rootid,zid) value(root,zid);
END LOOP;

CLOSE rs;
END;;
DELIMITER ;

「MySQL入門」RAND()でランダムなデータを作成する方法

方法1
SELECT *
FROM changfatbl AS r1 JOIN
(SELECT ROUND(RAND() *
(SELECT MAX(id)
FROM changfatbl)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 5;

方法2
SELECT t1.id
FROM producttbl AS t1 JOIN
(SELECT ROUND(RAND() *
((SELECT MAX(id) FROM itemstbl)-(SELECT MIN(id) FROM itemstbl))+
(SELECT MIN(id) FROM itemstbl)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 0,5;

1 / 812345...最後 »