搭建 CentOS 6 服务器(6) - MySQL、PostgreSQL
程序员文章站
2022-06-12 16:17:31
...
(一)MySQL
(1)安装
(2)基本设置
(3)主从设置
①设置Master
②设置Slave
③确认主从
主
从
(二)PostgreSQL
(1)下载安装
只安装Client:
(2)确认版本
(3)设置
数据库操作的历史记录文件
通过pgAdmin测试是否连接成功。
(4)启动设置
(5)创建登录用户
(6)创建数据库
(7)主从设置
①设置Master
②设置Slave
③确认主从
主
从
卸载PostgreSQL:
(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
推荐阅读
-
centos6利用yum安装php mysql gd的步骤
-
CentOS6 配置Nginx,MySql,php-fpm开机启动的方法
-
Centos6.x服务器配置jdk+tomcat+mysql环境(jsp+mysql)
-
CentOS6环境下搭建路由器的方法
-
centos下搭建SVN服务器和MySQL
-
CentOS6、7安装MySQL5.7全教程
-
Confluence 6 配置 MySQL 服务器的步骤教程
-
CentOS7 + node.js + nginx + MySQL搭建服务器全过程
-
centos 6.5下 mysql-community-server. 5.7.18-1.el6安装
-
CentOS服务器平台搭建mysql主从复制与读写分离的方法