搭建 CentOS 6 服务器(6) - MySQL、PostgreSQL

(一)MySQL

（1）安装

# yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
# yum info mysql-community-server
# yum -y install mysql-community-server
# vi /etc/my.cnf
    character-set-server=utf8
# /etc/init.d/mysqld restart
# mysqld --version
    mysqld  Ver 5.6.23 for Linux on x86_64 (MySQL Community Server (GPL))

（2）基本设置

# mysql_secure_installation
    Enter current password for root (enter for none):　←　回车

    Set root password? [Y/n]　←　回车
    New password:　←　123456
    Re-enter new password:　←　123456

    Remove anonymous users? [Y/n]　←　回车(删除匿名用户)

    Disallow root login remotely? [Y/n]　←　回车(禁止远程root登录)

    Remove test database and access to it? [Y/n]　←　回车(删除test库)

    Reload privilege tables now? [Y/n]　←　回车

    Thanks for using MySQL!
# mysql -u root -p
    Enter password:123456

    mysql> create database mydb;
    mysql> grant all privileges on mydb.* to testuser@localhost identified by '123456';
    mysql> select user, host from mysql.user;
    mysql> quit

（3）主从设置

①设置Master

# vi /etc/my.cnf
    [mysqld]
    server-id=10
    log-bin=mysqld-bin
    binlog-ignore-db=test,performance_schema,information_schema
# service mysqld restart
# mysql -u root -p
    mysql> grant replication slave on *.* to 'repl_user'@'slave_host' identified by 'repl_pwd';
    mysql> flush privileges;
    mysql> flush tables with read lock;
    mysql> quit
# mysqldump -u root -p --all-databases --lock-all-tables > /root/dump.sql
# gzip dump.sql
# mysql -u root -p
    mysql> unlock tables;
    mysql> show master status\G ←确认File和Position，后边Slave会用到
    mysql> quit

②设置Slave

# vi /etc/my.cnf
    [mysqld]
    server-id=11
    relay-log=mysqld-relay-bin
    read_only=1
# service mysqld restart
# scp root@slave_host:/root/dump.sql.gz /root
# cd /root
# gzip -d dump.sql.gz
# mysql -uroot -p < /root/dump.sql
# mysql -u root -p
    mysql> reset slave;
    mysql> change master to master_host='master_host', master_user='repl_user', master_password='repl_pwd', master_log_file='mysqld-bin.xxxxxx', master_log_pos=xxxx; ←这里的值和上边'show master status'的结果相同
    mysql> start slave;
    mysql> show slave status\G
    mysql> quit

③确认主从

主

# mysql -u root -p
    mysql> show databases;
    mysql> create database test_db;
    mysql> quit

从

# mysql -u root -p
    mysql> show databases; ←test_db同时被做成
    mysql> quit

(二)PostgreSQL

（1）下载安装

# cd /usr/local/src
# wget http://sourceforge.net/projects/postgresql.mirror/files/PostgreSQL%209.3.4/postgresql-9.3.4-1-linux-x64.run/download
# chmod 755 postgresql-9.3.4-1-linux-x64.run
# ./postgresql-9.3.4-1-linux-x64.run
    Installation Directory [/opt/PostgreSQL/9.2]:/usr/local/pgsql
    Data Directory [/usr/local/pgsql/data]: 
    Password :postgres
    Retype password :postgres
    Port [5432]:
    Please choose an option [1] : 430 ←选择对应的语言（430代表日语）
    Do you want to continue? [Y/n]: y
    Setup has finished installing PostgreSQL on your computer.

只安装Client：

# yum install readline-devel zlib-devel
# cd /usr/local/src/
# wget https://ftp.postgresql.org/pub/source/v9.3.4/postgresql-9.3.4.tar.gz
# tar xzvf postgresql-9.3.4.tar.gz
# cd postgresql-9.3.4
# ./configure --prefix /usr/local/pgsql
# make
# gmake -C src/bin install
# gmake -C src/include install
# gmake -C src/interfaces install
# gmake -C doc install

（2）确认版本

# id postgres
# su - postgres
# cd /usr/local/pgsql/bin
# ./psql --version

（3）设置

# cd /usr/local/pgsql/data
# cp postgresql.conf postgresql.conf.default
# vi postgresql.conf
    max_connections=100
    shared_buffers=128MB
    work_mem=16MB
# cp pg_hba.conf pg_hba.conf.default
# vi pg_hba.conf
    host    all         all         192.168.21.0/24        trust
# /etc/init.d/postgresql-9.3 restart

数据库操作的历史记录文件

#  touch /usr/local/pgsql/.psql_history
#  chown postgres:postgres /usr/local/pgsql/.psql_history

通过pgAdmin测试是否连接成功。

（4）启动设置

# chkconfig postgresql-9.3 on
# chkconfig --list postgresql-9.3

（5）创建登录用户

# su - postgres
# cd /usr/local/pgsql/bin
# ./createuser -s -d -r -l -P pguser
    Enter password for new role: 123456
    Enter it again: 123456
    Password :postgres
    ./psql
    Password :postgres
    postgres=# \du
                                 List of roles
     Role name |                   Attributes                   | Member of
    -----------+------------------------------------------------+-----------
     pguser    | Superuser, Create role, Create DB              | {}
     postgres  | Superuser, Create role, Create DB, Replication | {}
    postgres=# \q

（6）创建数据库

# ./createdb -E UTF8 -O pguser -U pguser pgdb
    Password :postgres
# ./psql -l
    Password :postgres
# ./psql -U pguser -d pgdb
    postgres=# \l
     List of databases
       Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
    -----------+----------+----------+------------+------------+----
     pgdb    | pguser       | UTF8     | ja_JP.utf8 | ja_JP.utf8 |
    postgres=# CREATE TABLE test_users(id integer NOT NULL, name character varying(10) NOT NULL, CONSTRAINT test_users_pkc PRIMARY KEY (id));
    postgres=# INSERT INTO test_users(id,name) VALUES (1, 'rensanning');
    postgres=# SELECT * FROM test_users;
     id | name
    ----+------
      1 | rensanning
    (1 row)
    postgres=# \q

（7）主从设置

①设置Master

# vi /usr/local/pgsql/data/postgresql.conf
    wal_level = hot_standby
    synchronous_commit = on
    max_wal_senders = 2
    synchronous_standby_names = 'slave1'
    wal_keep_segments = 100
# vi /usr/local/pgsql/data/pg_hba.conf
    local   replication     postgres                                trust
    host    replication     postgres        127.0.0.1/32            trust
# /etc/init.d/postgresql-9.3 restart

②设置Slave

# /etc/init.d/postgresql-9.3 stop
# /usr/local/pgsql/bin/pg_basebackup -U postgres -h master_host -p 5432 -D /usr/local/pg_slave --xlog --progress --verbose
# cp /usr/local/pgsql/data/postmaster.opts /usr/local/pg_slave/
# mv /usr/local/pgsql/data/ /usr/local/pgsql/data_bak/
# cp /usr/local/pg_slave/ /usr/local/pgsql/data/
# chown -R postgres:postgres /usr/local/pgsql/data/
# vi /usr/local/pgsql/data/postgresql.conf
    hot_standby = on
# cp /usr/local/pgsql/share/postgresql/recovery.conf.sample /usr/local/pgsql/data/recovery.conf
# vi /usr/local/pgsql/data/recovery.conf
    standby_mode = on
    primary_conninfo = 'host=master_host port=5432 application_name=slave1'
# /etc/init.d/postgresql-9.3 restart

③确认主从

主

# su - postgres
# cd /usr/local/pgsql/bin
# ./psql -U pguser -d pgdb -c "INSERT INTO test_users VALUES (2,'slave')"
    OK
# ./psql -U pguser -d pgdb -c "select * from test_users"
     id | name  
    ----+-------
      1 | rensanning
      2 | slave
    (2 rows)
# ./psql -U pguser -d pgdb -c "SELECT application_name,state,sync_priority,sync_state FROM pg_stat_replication;"
     application_name |   state   | sync_priority | sync_state 
    ------------------+-----------+---------------+------------
     slave1           | streaming |             1 | sync
     slave1           | streaming |             1 | potential

从

# su - postgres
# cd /usr/local/pgsql/bin
# ./psql -U pguser -d pgdb -c "INSERT INTO test_users VALUES (3,'slave')"
    ERROR:  cannot execute INSERT in a read-only transaction
# ./psql -U pguser -d pgdb -c "select * from test_users"
     id | name  
    ----+-------
      1 | rensanning
      2 | slave
    (2 rows)

卸载PostgreSQL：

# /etc/init.d/postgresql stop
# rm -f /etc/postgres-reg.ini
# rm -rf /usr/local/pgsql/
# rm -f /etc/init.d/postgresql
# vi /etc/ld.so.conf
 remove /usr/local/pgsql/lib
# rm /etc/ld.so.conf.d/postgresql.conf
# userdel -r postgres
# groupdel postgres