mysql源码安装

1. sudo apt-get install build-essential  
2. sudo apt-get install libncurses5-dev  
3. sudo apt-get install sysv-rc-conf  
4. sudo apt-get install cmake

创建mysql用户组

[plain] view plaincopy

1. sudo groupadd mysql  
2. sudo useradd -r -g mysql mysql

准备工作：

groupadd mysql

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

说明：

-g : 指定新用户所属的用户组(group)

-M : 不建立根目录

-s : 定义其使用的shell,/sbin/nologin代表用户不能登录系统

tar xvfmysql-5.6.16.tar.gz

cd mysql-5.6.16

cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc-DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1-DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

说明：

cmake \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\ #安装路径

-DMYSQL_DATADIR=/usr/local/mysql/data \ #数据文件存放位置

-DSYSCONFDIR=/etc \ #my.cnf路径

-DWITH_MYISAM_STORAGE_ENGINE=1 \ #支持MyIASM引擎

-DWITH_INNOBASE_STORAGE_ENGINE=1\ #支持InnoDB引擎

-DWITH_MEMORY_STORAGE_ENGINE=1\ #支持Memory引擎

-DWITH_READLINE=1 \ #快捷键功能(我没用过)

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \ #连接数据库socket路径

-DMYSQL_TCP_PORT=3306 \ #端口

-DENABLED_LOCAL_INFILE=1 \ #允许从本地导入数据

-DWITH_PARTITION_STORAGE_ENGINE=1 \ #安装支持数据库分区

-DEXTRA_CHARSETS=all \ #安装所有的字符集

-DDEFAULT_CHARSET=utf8 \ #默认字符

-DDEFAULT_COLLATION=utf8_general_ci

make

make install

完成以后，进行如下步骤：

cd /usr/local/mysql

chown -R mysql:mysql .(为了安全起见，安装完成后请修改权限给root用户)

scripts/mysql_install_db–user=mysql

chown -R root:mysql . (将权限设置给root用户，并设置给mysql组，取消其他用户的读写执行权限，仅留给mysql"rx"读执行权限，其他用户无任何权限)

chown -R mysql:mysql./data (给数据库存放目录设置成mysql用户mysql组，并赋予chmod-R ug+rwx 读写执行权限，其他用户权限一律删除仅给mysql用户权限)

cpsupport-files/my-default.cnf /etc/my.cnf (并给/etc/my.cnf+x权限同时删除其他用户的写权限，仅仅留给root和工作组rx权限,其他一律删除连rx权限都删除)

修改my.cnf配置
vim/etc/my.cnfGTEST_LIBRARIES:gmock;gtest

#[mysqld] 添加：

datadir =/usr/local/mysql/data

default-storage-engine=InnoDB

启动mysql

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &

或者直接用/usr/local/mysql/bin/mysqld启动，如：

sudo ./mysqld--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data--plugin-dir=/usr/local/mysql/lib/plugin --user=mysql--log-error=/usr/local/mysql/data/zhangchengyuan-pc.err--pid-file=/usr/local/mysql/data/zhangchengyuan-pc.pid --user=mysql

多个实例共用同一个my.cnf配置文件

datadir:    /data/3306    /data/3307

my.cnf:    /etc/my.cnf

：mysqld_multi

    要配置MySQL多实例，首先我们需要了解一下mysqld_multi这个脚本。mysqld_multi是管理多个mysqld的服务进程。这些服务进程用不同的unix socket或是监听于不同的端口，通过简单的命令，它可以启动，关闭和报告所管理的服务器的状态 。

加mysql用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -g mysql -s /sbin/nologin mysql

### 目录规划
[root@localhost ~]# mkdir -pv /mydata/data/330{6,7}
mkdir: created directory `/mydata'
mkdir: created directory `/mydata/data'
mkdir: created directory `/mydata/data/3306'
mkdir: created directory `/mydata/data/3307'

[root@localhost ~]# chown -R mysql:mysql /mydata/data/3306
[root@localhost ~]# chown -R mysql:mysql /mydata/data/3307

### 解压
[root@localhost ~]# tar xf mysql-5.5.38-linux2.6-i686.tar.gz  -C /usr/local/src

### 创建链接
[root@localhost local]# ln -sv src/mysql-5.5.38-linux2.6-i686 mysql
`mysql' -> `src/mysql-5.5.38-linux2.6-i686'

### 把bin添加到PATH
[root@localhost mysql]# vi /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost mysql]# . /etc/profile.d/mysql.sh
提供配置文件：/etc/my.cnf
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# cp support-files/my-small.cnf /etc/my.cnf
[root@localhost mysql]# vi /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port  = 3306
socket  = /tmp/mysql.sock

# Here follows entries for some specific programs
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /mydata/data/mysqld_multi.log
user = root

[mysqld3306]
port  = 3306
socket   = /tmp/mysqld_3306.sock
pid-file = /mydata/data/3306/mysqld.pid
datadir  = /mydata/data/3306
basedir  = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english

### These support master - master replication  
#auto-increment-increment = 4  
#auto-increment-offset = 1  # Since it is master 1  
# log-bin = /data/mysql/binlogs/bin-log-mysqld1  
# log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index  
#binlog-do-db = # Leave this blank if you want to control it on slave  
# max_binlog_size = 1024M 

[mysqld3307]
port            = 3307
socket          = /tmp/mysqld_3307.sock
pid-file        = /mydata/data/3307/mysqld.pid
datadir        = /mydata/data/3307
basedir        = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english

### Disable DNS lookups  
#skip-name-resolve 

### These support master - slave replication  
#log-bin = /data/mysql/binlogs/bin-log-mysqld2  
#log-bin-index = /data/mysql/binlogs/bin-log-mysqld2.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
#max_binlog_size = 1024M
### Relay log settings
#relay-log = /data/mysql/log/relay-log-mysqld2
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G

### Slow query log settings
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes

# The MySQL server
[mysqld]
port  = 3306
socket  = /tmp/mysql.sock
pid-file = /mydata/data/mysqld.pid
datadir  = /mydata/data
basedir  = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english

skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

### Incrase the max connections
max_connections = 200

### Set expiration time for logs, including binlogs
expire_logs_days = 14

### set the character as utf8
character-set-server = utf8
collation-server = utf8_unicode_ci

#skip-networking
server-id = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

### set engine
default-storage-engine = INNODB

## enable per table data for innodb to shrink ibdata
innodb_file_per_table = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

[mysql.server]
user = mysql

[mysqld_safe]
log-error = /mydata/data/mysqld.log
pid-file  = /mydata/data/mysqld.pid
open-files-limit = 8192

 

初始化实例：
### mysqld3306
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3306 --user=mysql
### mysqld3307
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3307 --user=mysql
提供多实例管理脚本：
[root@localhost mysql]# cp support-files/mysqld_multi.server /etc/init.d/
[root@localhost mysql]# vi /etc/init.d/mysqld_multi.server

#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin

conf = /etc/my.cnf
export PATH=$PATH:$bindir

if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi

case "$1" in
    'start' )
        "$mysqld_multi" --defaults-extra-file=$conf  start $2
        ;;
    'stop' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        ;;
    'report' )
        "$mysqld_multi" --defaults-extra-file=$conf report $2
        ;;
    'restart' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac
管理实例：

 -- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。

同时启动mysqld3306, mysqld3307

[root@localhost mysql]# /etc/init.d/mysqld_multi.server start 3306,3307

### 我们看到两个实例已经启动成功
[root@localhost mysql]# netstat -tulpn | grep -i mysql
tcp        0      0 0.0.0.0:3306          0.0.0.0:*        LISTEN      2876/mysqld        
tcp        0      0 0.0.0.0:3307          0.0.0.0:*        LISTEN      2877/mysqld
同时关闭mysqld3306, mysqld3307
### 先不要操作哈， 等测试登录后再关闭
[root@localhost mysql]# /etc/init.d/mysqld_multi.server stop 3306,3307
登录实例：

### 注意，因为这里mysql的root用户并没有设置密码，所以可以直接登录
### 登录mysqld3306
[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3306.sock

### 登录mysqld3307
[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3307.sock

### 或者
[root@localhost mysql]# mysql -uroot -h127.0.0.1 -P3306

GRANT REPLICATION SLAVE ON *.* TO 'AAAA'@'%' IDENTIFIED BY 'qq!!QQ123456';

flush privileges;

change master to master_host='localhost',master_user='AAAA',master_password='qq!!QQ123456',master_log_file='mysql-bin.000009' ,master_log_pos=120;    #执行同步语句

show processlist

主从切换

1 确认从服务器已经完成所有同步操作：

 

stop slave io_thread  

show processlist 

 

直到看到状态都为：xxx has read all relay log 表示更新都执行完毕

2 停止从服务器slave服务

stop slave

3 将从服务器切换为主服务器：

 

reset master 

完成切换。

server-id=3307
port=3307
user=mysql
pid-file=/home/coldwater/experiment/mysql-1/mysqld.pid
basedir=/home/coldwater/experiment/mysql/mysql-5.6
datadir=/home/coldwater/experiment/mysql-1/data
innodb_log_group_home_dir=/home/coldwater/experiment/mysql-1/data/innodb_log
tmpdir=/home/coldwater//experiment/mysql-1/data/tmp
slave_load_tmpdir=/home/coldwater/experiment/mysql-1/data/tmp
innodb-file-per-table=1
binlog-format=row
max-binlog-size=512m
log-bin=/home/coldwater/experiment/mysql-1/data/mysql_binlog/master_bin
log-bin-index=/home/coldwater/experiment/mysql-1/data/mysql_binlog/master_bin.index
relay-log=/home/coldwater/experiment/mysql-1/data/mysql_binlog/relay_log
relay-log-index=/home/coldwater/experiment/mysql-1/data/mysql_binlog/relay_log.index
socket=/home/coldwater/experiment/mysql-1/data/mysql.sock
slow-query-log
slow-query-log-file=/home/coldwater/experiment/mysql-1/data/mysql_log/mysqld_slow_query.log
log-error=/home/coldwater/experiment/mysql-1/data/mysql_log/mysqld_error.log
sync-binlog=1
general-log
general-log-file=/home/coldwater/experiment/mysql-1/data/mysql_log/mysqld.log
max-connections=1024
collation-server=utf8_general_ci
character-set-server=utf8
log-slave-updates
#skip-name-resolve
innodb_buffer_pool_size=487790694
# innodb_log_buffer_size=8M
innodb_log_file_size=214748464
innodb_log_files_in_group=2
innodb_write_io_threads=10
innodb_read_io_threads=10
expire_logs_days=7