mysql5.0 linux_MySQL（5.0~5.7）Linux环境

安装服务

1.MySQL-5.0.40

1.1.Source Installation Overview(lines 74 of install-source)

系统默认可能会安装三个mysql的包：

mysql-libs

mysql

mysql-devel

从下往上依赖关系。

源码包安装mysql

#检查环境

rpm-aq | grepmysql

rpm-ql mysql

rpm-qf /etc/my.conf

rpm-qc mysql-libs

rpm-qR mysql-libs

rpm-qd mysql

rpm-qi mysql

netstat-nltfind / -name mysqlfind / -name my.confcat /etc/passwd | grepmysqlcat /etc/group |rpm-qa | grep gccrpm-qa | grep make

ls /home

添加用户

groupadd-r mysql

useradd-g mysql -M -r -s /sbin/nologin mysql

开始安装vi INSTALL-SOURCE

./configure --prefix=/usr/local/mysqlecho $?

make

echo $?

make install

echo $?配置文件cp support-files/my-medium.cnf /etc/my.conf

目录权限

cd/usr/local/mysqlchown -R mysql .chgrp -R mysql .

初始化数据库

bin/mysql_install_db --user=mysql

开机启动cp /support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/init.d/mysqld

安全启动服务/把数据库的数据文件定义到其他磁盘设备上

bin/mysqld_safe --user=mysql --datadir=/dell/mysql_data

View Code

1.2.Typical ‘configure’Options(lines 265 of  install-source)

配置

./configure --help

./configure -h

#仅仅编译客户端程序

./configure --without-server

#默认安装的目录/usr/local(数据目录/usr/local/var)。可以改写为：

./configure --prefix=/usr/local/mysql

./configure --prefix=/usr/local/ --localstatedir=/usr/local/mysql/data

#使用unix的套接字方式连接数据库：

./configure --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock

#有gcc，没有c++库，可以使用gcc来作为c++的编译器：

CC=gcc CXX=gcc ./configure

./configure --with-charset=gb2312

./configure --with-charset=utf8

./configure --with-charset=latin1(这个是默认值)

./configure --with-collation=latin1_swedish_ci(这个是排序方法的默认值)

./configure --with-extra-charsets=(空格分开)

./configure --with-extra-charsets=all

#重新编译一个源码树时：rmconfig.cachemakecleanmake dist

View Code

＿＿＿＿＿＿＿＿＿＿＿＿＿

Mysql-5.1.72.tar.gz

安装方法跟上边的大同小异。

＿＿＿＿＿＿＿＿＿＿＿＿＿

2.MySQL-5.5.22

2.1.Installing MySQL from Generic Binaries on Unix/Linux

系统默认可能会安装三个mysql的包：

mysql-libs

mysql

mysql-devel

从下往上依赖关系。

二进制文件安装mysql

#检查环境

rpm-aq | grepmysql

rpm-ql mysql

rpm-qf /etc/my.conf

rpm-qc mysql-libs

rpm-qR mysql-libs

rpm-qd mysql

rpm-qi mysql

netstat-nltfind / -name mysqlfind / -name my.confcat /etc/passwd | grepmysqlcat /etc/group |rpm-qa | grep gccrpm-qa | grep make

ls /home

#添加用户

groupadd mysql

useradd-r -g mysql -M -s /sbin/nologin mysql

#开始安装

cd/usr/localtar -zxvf ……

cd mysqlchown -R mysql .chgrp -R mysql .

#初始化数据库

scripts/mysql_install_db --user=mysqlchown -R root .chown -R mysql data

#配置操作cp support-files/my-medium.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/init.d/mysqld

#启动服务

bin/mysqld_safe --user=mysql --datadir=/dell/mysql_data

View Code

my.cnf

1[mysqld]2 basedir=/mysql/mysql3 datadir=/mysql/mysql/data

View Code

2.2.Installing MySQL from Source

源码包安装mysql

#检查环境

rpm-aq | grepmysql

rpm-ql mysql

rpm-qf /etc/my.conf

rpm-qc mysql-libs

rpm-qR mysql-libs

rpm-qd mysql

rpm-qi mysql

netstat-nltfind / -name mysqlfind / -name my.confcat /etc/passwd | grepmysqlcat /etc/group |rpm-qa | grep gccrpm-qa | grep make

ls /home

#添加用户

groupadd mysql

useradd-r -g mysql -M -s /sbin/nologin mysql

#配置、编译

ccmake .(先进行交互式配置)

cmake .make

make installcd/usr/local/mysqlchown -R mysql .chgrp -R mysql .

#初始化数据库

scripts/mysql_install_db --user=mysqlchown -R root .chown -R mysql datacp support-files/my-medium.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/rc.d/init.d/mysqld

启动服务

bin/mysqld_safe --user=mysql --datadir=/dell/mysql_data

View Code

2.3.To list the configuration options,use one of the following.

配置选项

cmake . -L# overview

cmake .-LH# overview with help text

cmake .-LAH# all params with help text

ccmake .# interactive displaymakecleanrm CMakeCache.txt

View Code

2.4.rpm

To see all files in an RPM packet,run a command like this:

rpm -qpl MySQL-server-VERSION.glibc23.i386.rpm

rpm-ivh MySQL-server-VERSION.glibc23.i386.rpm

rpm-ivh MySQL-client-VERSION.glibc23.i386.rpm

# Start from a source RPM,run:

rpmbuild--rebuild --clean MySQL-VERSION.src.rpm

View Code

2.5.安装Cmake-2.8.12.tar.gz

cmake

viReadme.txt

./bootstrapmake

make install

View Code

3.MySQL-5.5.34

3.1. Installing MySQL on Unix/Linux Using Generic Binaries

二进制包安装mysql

#检查当前环境配置

rpm-aq | grepmysql

rpm-ql mysql

rpm-qf /etc/my.conf

rpm-qc mysql-libs

rpm-qR mysql-libs

rpm-qd mysql

rpm-qi mysql

netstat-nltfind / -name mysqlfind / -name my.confcat /etc/passwd | grepmysqlcat /etc/group |rpm-qa | grep gccrpm-qa | grep make#添加用户

groupadd mysql

useradd-r -g mysql -M -s /sbin/nologin mysql

cd/usr/local

#开始安装tar -zxvf ……

cd mysql

#设定目录权限chown -R mysql .chgrp -R mysql .

#初始化数据库

scripts/mysql_install_db --user=mysql

#设定库文件目录权限chown -R root .chown -R mysql data

#添加配置文件cp support-files/my-medium.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/init.d/mysqld

#启动服务

bin/mysqld_safe --user=mysql --datadir=/dell/mysql_data

View Code

3.2.Installing MySQL Using a Standard Source Distribution

源码包安装mysql

rpm -aq | grepmysql

rpm-ql mysql

rpm-qf /etc/my.conf

rpm-qc mysql-libs

rpm-qR mysql-libs

rpm-qd mysql

rpm-qi mysql

netstat-nltfind / -name mysqlfind / -name my.confcat /etc/passwd | grepmysqlcat /etc/group |rpm-qa | grep gccrpm-qa | grep make

ls /home

groupadd mysql

useradd-r -g mysql -M -s /sbin/nologin mysql

cd mysql

ccmake . #没有这一步也过去了

cmake .make

make installcd/usr/local/mysqlchown -R mysql .chgrp -R mysql .

scripts/mysql_install_db --user=mysqlchown -R root .chown -R mysql datacp support-files/my-medium.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/init.d/mysqld

bin/mysqld_safe --user=mysql --datadir=/dell/mysql_data

View Code

4.MySQL-5.6.36

二进制安装

#添加用户

[root@tri blog]# groupadd-g 51mysql

[root@tri blog]# useradd-r -u 51 -g 51 -d /data02/blog/mysql_data -s /sbin/nologin mysql

#开始安装

[root@tri local]#tar -xf /opt/data01/tars/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

[root@tri local]#ln -s mysql-5.6.36-linux-glibc2.5-x86_64/mysql

[root@tri local]# cd mysql

[root@tri mysql]#chown -R mysql:mysql .

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

[root@tri mysql]#chown -R root .

[root@tri mysql]#chown -R mysql data/#标准启动

[root@tri mysql]# bin/mysqld_safe --user=mysql

#直接启动

[root@tri mysql]# bin/mysqld_safe --user=mysql --bind-address=127.0.0.1 --port 3333#解包后查看文件，编译时配置“-prefix=”

[root@tri mysql]#vi docs/INFO_BIN

CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql

MYSQL_DATADIR:PATH=/usr/local/mysql/data

#添加启动时打开服务

[root@tri mysql]#cp support-files/mysql.server /etc/init.d/mysql

#打开上述文件(提示配置文件、主目录、数据目录)

[root@tri mysql]#vi /etc/init.d/mysql

# If youinstall MySQL on some other places than /usr/local/mysql, thenyou

# have todo one of the following things forthis script to work:

#

#-Run this script from within the MySQL installation directory

#- Create a /etc/my.cnf filewith the following information:

# [mysqld]

# basedir=#- Add the above to any other configuration file (for example ~/.my.ini)

# and copy my_print_defaults to/usr/bin

#- Add the path to the mysql-installation-directory to the basedir variable

# below.

#

# If you want to affect other MySQL variables, you shouldmakeyour changes

#in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

# If you change basedir, you must also change datadir. These may get

# overwritten by settingsinthe MySQL configuration files.

basedir=datadir=

View Code

5.Mysql-5.7.28

二进制安装

shell>groupadd mysql

shell> useradd -r -g mysql -s /bin/falsemysql

shell> cd /usr/local

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

shell> ln -s full-path-to-mysql-VERSION-OS mysql

shell>cd mysql

shell> mkdir mysql-files

shell> chown mysql:mysql mysql-files

shell> chmod 750 mysql-files

shell> bin/mysqld --initialize --user=mysql

shell> bin/mysql_ssl_rsa_setup

shell> bin/mysqld_safe --user=mysql Next command is optional

shell> cp support-files/mysql.server /etc/init.d/mysql.server

View Code

处理默认账户

修改 root 密码1

shell> mysql -u root

mysql> SET PASSWORD = PASSWORD('newpwd');

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

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

View Code

修改root密码2

shell> mysql -u root

mysql> UPDATE mysql.user SET password = PASSWORD('newpwd)

-> WHERE user = 'root';

(没有这步，就得重启后才能生效)

mysql>FLUSH PRIVILEGES;

shell> mysqladmin -u root password "newpwd"shell> mysqladmin -u root -h host_name password "newpwd"mysqladmin 无法对127.0.0.1起作用。

shell> mysqladmin -u root -p shutdown

View Code

修改 anonymous 密码

修改 anonymous 密码

shell> mysql -u root -p

mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

shell> mysql -u root -p

mysql> UPDATE mysql.user SET password = PASSWORD('newpwd')-> WHERE user = '';

mysql> FLUSH PRIVILEGES;

View Code

删除 anonymous 用户

删除 anonymous 用户

shell> mysql -u root -p

mysql> DROP USER ''@'localhost';

mysql> DROP USER ''@'host_name';

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

View Code

拒绝任意用户访问测试数据库

shell> mysql -u root -p

mysql> DELETE FROM mysql.db WHERE db LIKE 'test%';

mysql>FLUSH PRIVILEGES;

mysql> DROP DATABASE test; (* 再进一步，连测试库都干掉)

View Code

多实例的实现

实现1

编译不同配置的服务器，指定不同的 tcp/ip 端口 和 套接字 文件。 指定不同的 basedir，这会影响到 数据目录、日志文件、pid文件。

# 编译新的资源

cmake .-DMYSQL_TCP_PORT=3307\-DMYSQL_UNIX_ADDR=\-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.31

View Code

实现2

新实例仅仅需要不同的端口和套接字之外，别无他求时。只需要指定不同的启动参数即可

/usr/local/mysql/bin/mysqld_safe --socket=/tmp/my02.cnf --port=3000 --datadir=/data/

/usr/local/mysql/bin/mysql -P3000 -uroot -p

View Code

指定新的：端口、socket文件、数据目录【最低要求】

选项：错误日志，PID 文件

# 路径跟 datadir 走--log-error=develop-database.err--pid-file=db.pid

View Code

创建数据目录

新建

# 需要一个空目录mkdirxxxchownmysql:mysql xxx

# 创建数据目录

bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/xxx --user=mysql

View Code

拷贝

# 1.停止例程运行

#2.拷贝数据目录

View Code

服务托管(chkconfig)

启动脚本

# 1.使用 support-files/mysql.server 进行编辑cp mysql.server /etc/init.d/mysql.serverchmod +x /etc/init.d/mysql.server

chkconfig--add mysql.server

chkconfig--level 345 mysql.server on

View Code

添加配置选项

# 在全局为其添加选项vi /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/var

socket=/var/tmp/mysql.sock

port=3306user=mysql

[mysql.server]

basedir=/usr/local/mysql

View Code

mysql.server 会同时读取 【mysqld】和【mysql.server】的配置信息。

RPM

-----------------------------

使用 RPM 包安装数据库

RPM 包可以是官方提供的，也可以是其他作者提供的；可能有所不同(文件结构)。

标准安装，需要 MySQL-server & MySQL-client 。(其他的包在标准安装中不需要)

4109 行有关于各种包(名称)代表含义的详细说明。(包对 CPU 是有选择的 4208 )

查看 RPM 包里的文件：

shell> rpm -qpl MySQL-server-VERSION.glibc23.i386.rpm

-----------------------------

* Red Hat Linux, Fedora, CentOS

root-shell> yum install mysql mysql-server mysql-libs

root-shell> service mysqld start

root-shell> chkconfig --levels 235 mysqld on

-----------------------------

Debian,Ubuntu,Kubuntu

root-shell> apt-get install mysql-client-5.1 mysql-server-5.1

root-shell> service mysql start | stop

-----------------------------

优化从编译个出色的mysqld开始

使用最好的编译器、和最佳的编译选项；使用静态模板编译。这个很重要，性能能提升10-30%。

直接在官网下载的二进制包，包含所有的字符集；自己编译可以选择需要的字符集。

Here is a list of some measurements that we have made:* If you link dynamically (without -static), the result is 13%slower on Linux. Note that you still can use a dynamically

linked MySQL libraryforyour client applications. It is the

server that is most criticalforperformance.*For a connection from a client to a server running on the same

host,if you connect using TCP/IP rather than a Unix socketfile, performance is 7.5% slower. (On Unix, ifyou connect to

the host name localhost, MySQL uses a socketfileby default.)* For TCP/IP connections from a client to a server, connecting

to a remote server on another host is8% to 11%slower than

connecting to a server on the same host, evenforconnections

faster than 100Mb/s Ethernet.*When running our benchmark tests using secure connections (all

data encrypted with internal SSL support) performance was55%slower than with unencrypted connections.* On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0is4% faster than one compiled with gcc 3.2.* On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0is4% faster in 32-bit mode than in 64-bit mode.* Compiling on Linux-x86 using gccwithout frame pointers

(-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp)

makes mysqld1% to 4% faster.

View Code

MySQL