old mysql_L001-oldboy-mysql-dba-lesson01

L001-oldboy-mysql-dba-lesson01

《sql应用重构》经典的书，思想，封顶境界！

[root@web01 ~]# cd /usr/local/src/

[root@web01 src]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz

[root@web01 src]# cp -R mysql-5.5.32-linux2.6-x86_64

/usr/local/mysql

[root@web01 src]# ll /usr/local/mysql

[root@web01 ~]# cp /usr/local/mysql/support-files/my-huge.cnf

/etc/my.cnf

[root@web01 ~]# mkdir /var/lib/mysql

[root@web01 ~]# mv /etc/my.cnf /var/lib/mysql/

[root@web01~]# vi /var/lib/mysql/my.cnf +40

21 default-character-set = utf8

40 datadir

= /var/lib/mysql

41 character_set_server = utf8

139 default-character = utf8

[root@web01 ~]# useradd -M -s /sbin/nologin mysql

[root@web01 ~]# id mysql

[root@web01 ~]# chown -R mysql:mysql /var/lib/mysql/

[root@web01~]# cp /usr/local/mysql/support-files/mysql.server

/etc/init.d/mysqld

[root@web01 mysql]# cd /usr/local/mysql

[root@web01 mysql]# ./scripts/mysql_install_db --user=mysql

[root@web01~]#

/usr/local/mysql/scripts/mysql_install_db  --defaults-file=/var/lib/mysql/my.cnf --user=mysql  #mysql初始化

[root@web01 ~]#

echo '/usr/local/mysql/bin/mysqld_safe

--defaults-file=/var/lib/mysql/my.cnf &' >>/etc/rc.local

[root@web01 ~]#

/usr/local/mysql/bin/mysqld_safe

--defaults-file=/var/lib/mysql/my.cnf &

[root@web01 ~]# ps -ef|grep mysql

#或者使用/etc/init.d来启动mysql

[root@web01 ~]# cp

/usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@web01 ~]# tailf /var/lib/mysql/web01.com.err  #启动后，这个日志一定要好好看看。

[root@web01 ~]# ln -s

/usr/local/mysql/bin/mysqladmin /bin/mysqladmin

[root@web01 ~]# ln -s

/usr/local/mysql/bin/mysqldump /bin/mysqldump

[root@web01 ~]# ln -s

/usr/local/mysql/bin/mysql /bin/mysql

[root@web01 ~]# ln -s /usr/local/mysql/bin/mysqld_safe /bin/mysqld_safe

[root@web01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog/bin/mysqlbinlog

[root@web01 ~]# mysql -u root -S /tmp/mysql.sock

mysql>

use mysql;

show tables;

select * from user\G;

[root@web01 ~]# mysqladmin -u root password 'testpassword'   #设置root密码

[root@web01 ~]# mysql -uroot -ptestpassword

mysql> select user,host,password from mysql.user;

delete from mysql.user where user='root' and host='web01.com';

delete from mysql.user where user='root' and host='::1';

use mysql; update user set password='*9F69E47E519D9CA02116BF5796684F7D0D45F8FA'

where host='127.0.0.1';

select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host      |

password

|

+------+-----------+-------------------------------------------+

| root | localhost | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |

| root | 127.0.0.1 | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |

+------+-----------+-------------------------------------------+

create database mywebsite;

show databases;

grant all on mywebsite.* to 'test'@'10.0.0.%' identified by 'test';

flush privileges;

show global variables like '%char%';

select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host      |

password

|

+------+-----------+-------------------------------------------+

| root | localhost | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |

| root | 127.0.0.1 | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |

| test | 10.0.0.%  | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |

+------+-----------+-------------------------------------------+

update mysql.user set

password=password('newpassword') where user='test';