インストール

sudo aptitude install mysql-server

alive check

mysqladmin ping -u root -p

mysqlパスワード変更

mysql -u root -p
    rootパスワード入力
SET PASSWORD FOR root@localhost=PASSWORD('new_password');

ユーザ確認

ログイン

mysql -u root -p

SQL実行

SELECT host,user,password FROM mysql.user;

文字コード設定

charset UTF8 check

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (armv7l) using readline 6.2

Connection id:      39
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.38-0+wheezy1 (Debian)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         5 hours 20 min 7 sec

Threads: 1  Questions: 114  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 41  Queries per second avg: 0.005

if your env is not seted to utf8, add utf8 settings below

sudo vim /etc/mysql/conf.d/character_set.cnf

[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8
skip-character-set-client-handshake
[mysqldump]
default-character-set = utf8
[mysql]
default-character-set = utf8

restart mysqld

sudo service mysql restart

and recheck with mysql> status

データ移行

move mysql data directory

cd /data/data/
mkdir 60.mysql
chown mysql:mysql 60.mysql
mkdir mysqldata mysqldump
chown mysql:mysql mysqldata
chown mysql:mysql mysqldump
sudo cp -fprv /var/lib/mysql/ ./mysqldata/

start server

sudo service mysql start

Database Restore

Create User

mysql -u root -p
----
select User,Host from mysql.user;

CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';

grant all on *.* to '[username]'@'localhost' identified by '[password]';
exit

Create database

mysql -u [username] -h [hostname] -p
show databases;
create database [databasename] default character set utf8;
show create database [databasename];
use [databasename];
show tables;

Data Restore!

mysql -u [username] -p [databasename] < database_backup_filename.sql

不要となったデータを削除

sudo rm /data/tmp

!!! Warning !!! ======================================

Below setting is Not working!

Data move and setting new wheezy mysql 5.5 server from old lenny mysql 5.1 server

!!! Warning !!! ======================================

Mysql data directory setting

mysql停止

sudo service mysql stop

before server’s mysql data directory permission setting.

cd /data/data/60.mysql
sudo chown -R mysql:mysql ./mysqldata

ディレクトリ指定設定(add file)

sudo vim /etc/mysql/conf.d/my_datadir.cnf

[mysqld]
datadir = /data/data/60.mysql/mysqldata/mysql

正しく設定変更され起動できるか確認する為、元のデータは別場所へ移動

sudo mkdir /data/tmp
sudo mv /var/lib/mysql /data/tmp

mysql 起動

sudo service mysql start
----Error!!!
ls-xhle74:wheezy:$ ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Devian password is old system setting.. change password!

investigate old system password for debian-sys-maint

log in old debian system, and see password below command.

sudo cat /etc/mysql/debian.cnf

memo this password, and login with below

mysql -u debian-sys-maint -p

chage password for debian-sys-maint with current system password.

investigate current system password with same command sudo cat /etc/mysql/debian.cnf
and reset password for debian-sys-maint.

SET PASSWORD FOR 'debian-sys-maint'@'localhost'=PASSWORD('current_system_password');

start server

sudo service mysql start