Mysql - cruisechang/wiki-linux GitHub Wiki

Basic

Installation

官網

Great Tutorial

  1. Using web browser visit https://dev.mysql.com/downloads/repo/yum/

從mysql官網取得yum repo資料，找出要安裝的版本的yum repo，下載rpm

例如： (mysql57-community-release-el7-11.noarch.rpm)

例如： (mysql57-community-release-el5-8.noarch.rpm)

  1. 用wget抓rpm
wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
  1. 使用yum安裝rpm
sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm
  1. 安裝mysql
sudo yum install mysql-community-server

Start

//start
systemctl start myslqd or service start mysqld
//stop
systemctl stop mysqld or service stop mysqld
//enable
systemctl enable mysqld
//status
systemctl status mysqld

Initial secure operation

1.有可能還沒啟動，先啟動mysqld

systemctl enable mysqld
systemctl start mysqld
  1. 啟動後，會產生一個暫時用的密碼，在這個位置
sudo grep 'temporary password' /var/log/myslqd.log
  1. mysql 8.0 移除密碼認證(嚴格密碼): Install / uninstall component validate password
先login mysql

INSTALL COMPONENT 'file://component_validate_password';
UNINSTALL COMPONENT 'file://component_validate_password';

或是更改密碼強度
SET GLOBAL validate_password.policy = 0;   // For LOW
SET GLOBAL validate_password.policy = 1;   // For MEDIUM
SET GLOBAL validate_password.policy = 2;   // For HIGH
  1. 使用剛才的密碼run secure script
sudo mysql_secure_installation

會進行重置root密碼 / 移除anonymous user / 限制root remote login / remove test db and access to it / reload privilege tables

Testing MySQL

Run mysqladmin to show version

mysqladmin -u root -p version

Operations

Change password

mysql> alert user 'root'@'localhost' IDENTIFIED BY 'newpassword';

root remote access from any host:

  1. grant all privileges
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'  IDENTIFIED BY 'your_root_password'  WITH GRANT OPTION;
FLUSH PRIVILEGES;`
  1. sudo service mysql restart

Table operation

Create new table

create table tableName(id int(3) not null auto_increment,user_id int(10),session_id varchar(40), primary key(id));

Create new column in table

alter table tableName add columnName datetime default current_timestamp;

Change table

Need name,type and default setting.

alter table tableName change oldColumnName newColumnName datetime default current_timestamp;

DB Operation

Create db

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

Show all db

mysql> show databases;

Backup DB

//backup target DB
mysqldump --user=root -p mydb > /backup/mydb.sql

//backup all DB
mysqldump --user=root -p --all-databases > /backup/mysql.sql

Restore DB

mysql -u root -p mydb < /backup/mydb.sql

use -R and --triggers to keep the routines and triggers of original database.

or

https://stackoverflow.com/questions/19483087/importing-large-sql-file-to-mysql-via-command-line

mysql>use your_db
mysql>SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;

//set autocommit=0, stop autocommit

reset root password (MAC)

If you don't remember the password you set for root and need to reset it, follow these steps:

Stop the mysqld server, this varies per install Run the server in safe mode with privilege bypass sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables;

In a new window connect to the database, set a new password and flush the permissions & quit: mysql -u root

  • For MySQL older than MySQL 5.7 use:

UPDATE /usr/local/mysql/bin/mysql.user SET Password=PASSWORD('your-password') WHERE User='root';

  • For MySQL 5.7+ use:
USE mysql;

UPDATE /usr/local/mysql/bin/mysql.user SET authentication_string=PASSWORD("your-password") WHERE User='root';

Refresh and quit:

FLUSH PRIVILEGES;

\q

Stop the safe mode server and start your regular server back. The new password should work now. Worked like a charm for me :)

php connect apache(new MySQLi只能設定127.0.0.1的問題)

当主机填写为localhost时MySQL会采用 unix domain socket连接，

当主机填写为127.0.0.1时MySQL会采用TCP/IP的方式连接。

使用Unix socket的连接比TCP/IP的连接更加快速与安全。这是MySQL连接的特性，

可以参考官方文档的说明4.2.2. Connecting to the MySQL Server：

  • 使用TCP/IP代替Unix socket。即在连接的时候将localhost换成127.0.0.1。
  • 修改MySQL的配置文件my.cnf，指定mysql.socket的位置：
  • /var/lib/mysql/mysql.sock (你的mysql.socket路径)。
  • 直接在php建立连接的时候指定my.socket的位置（官方文档：mysqli_connect）。比如：
  • $db = new MySQLi('localhost', 'root', 'root', 'my_db', '3306', '/var/run/mysqld/mysqld.sock')

Completely remove

https://gist.github.com/vitorbritto/0555879fe4414d18569d

fix mysql.sock not exist

mysqld stop
mysql.server start