维基百科关于MariaDB的介绍：
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，10.0.9版起使用XtraDB（名称代号为Aria）来代替MySQL的InnoDB。
创建mysql普通账号，设置数据库存储数据的目录,设置权限。
[root@DB ~]# groupadd -r mysql [root@DB ~]# useradd -r -g mysql -s /sbin/nologin mysql [root@DB ~]# mkdir -p /data/mydata [root@DB ~]# chown -R mysql:mysql /data
2.安装一下数据库依赖的一些软件包。
yum install -y gcc gcc-c++ make cmake ncurses ncurses libxml2 libxml2-devel openssl-devel bison bison-devel ncurses-devel
3.下载Mariadb源码包，解压,编译,安装。
[root@DB ~]# mkdir -p /taokey/tools [root@DB ~]# cd /taokey/tools/ [root@DB tools]# wget http://mirrors.opencas.cn/mariadb/mariadb-galera-10.0.17/source/mariadb-galera-10.0.17.tar.gz [root@DB tools]# tar -zxf mariadb-galera-10.0.17.tar.gz [root@DB tools]# cd mariadb-10.0.17/ [root@DB mariadb-10.0.17]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STPRAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWIYH_READLINE=1 -DWIYH_SSL=system -DVITH_ZLIB=system -DWITH_LOBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci [root@DB mariadb-10.0.17]# make && make install
4.拷贝数据库启动脚本到/etc/rc.d/init.d/mysqld目录下，修改/etc/my.cnf配置文件。
[root@DB mariadb-10.0.17]# cd /usr/local/mysql/ [root@DB mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld [root@DB mysql]# chmod +x /etc/rc.d/init.d/mysqld [root@DB mysql]# cp support-files/my-large.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? yes [root@DB mysql]# vim /etc/my.cnf +42 datadir = /data/mydata
5.初始化数据库，启动数据库。
[root@DB mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mydata/ [root@DB mysql]# service mysqld start Starting MySQL.. SUCCESS!
6.设置数据库的系统变量。
[root@DB mysql]# cat /etc/profile.d/mysqld.sh export PATH=$PATH:/usr/local/mysql/bin [root@DB mysql]# source /etc/profile.d/mysqld.sh [root@DB mysql]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 10.0.17-MariaDB-wsrep-log Source distribution, wsrep_25.10.r4144 Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
mariaDB主从复制：
1.实验环境：
服务器版本：
CentOS 6.5 x86_64
数据库版本：
mariadb-10.0.17
服务器主机名、IP地址：
db_master 192.168.3.113
db_slave 192.168.3.114
安装MariaDB数据库，请参照上文。
2.修改db_master上的my.cnf配置文件如下：
[root@db_master ~]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir = /data/mydata log-bin=mysql-bin binlog_format=mixed server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
修改db_slave上的my.cnf配置文件如下：
[root@db_slave mysql]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir = /data/mydata log-bin=mysql-bin binlog_format=mixed server-id = 2 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
3.在db_master上，创建复制用户和密码，设置相应权限，刷新权限表。
MariaDB [(none)]> grant replication client,replication slave on *.* to 'taokey'@'192.168.3.%' identified by '123.com'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 651 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
4.在db_slave上，使用change命令导入数据，启动slave，然后查看slave状态。
MariaDB [(none)]> change master to master_host='192.168.3.113',master_user='taokey',master_password='123.com',master_log_file='mysql-bin.000004',master_log_pos=651,master_connect_retry=5,master_heartbeat_period=2; Query OK, 0 rows affected (0.09 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.113 Master_User: taokey Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 651 Relay_Log_File: db_slave-relay-bin.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 651 Relay_Log_Space: 835 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)
5.在db_master主库上创建beyond数据库。
MariaDB [(none)]> create database beyond; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | beyond | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
6.在从库db_slave上查看下数据库，以及从库状态。
[root@db_slave mysql]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 10.0.17-MariaDB-wsrep-log Source distribution, wsrep_25.10.r4144 Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | beyond | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.113 Master_User: taokey Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 776 Relay_Log_File: db_slave-relay-bin.000002 Relay_Log_Pos: 660 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 776 Relay_Log_Space: 960 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)
7.在db_master上，beyond数据库中创建一个lc_course数据表。
MariaDB [(none)]> use beyond; Database changed MariaDB [beyond]> create table lc_course( id int, course_name varchar(100), course_length int, teacher varchar(50), category varchar(100)); Query OK, 0 rows affected (0.14 sec) MariaDB [beyond]> show tables; +------------------+ | Tables_in_beyond | +------------------+ | lc_course | +------------------+ 1 row in set (0.00 sec)
8.在db_slave从库上查看复制的结果和状态。
MariaDB [(none)]> use beyond; Database changed MariaDB [beyond]> show tables; +------------------+ | Tables_in_beyond | +------------------+ | lc_course | +------------------+ 1 row in set (0.00 sec) MariaDB [beyond]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.113 Master_User: taokey Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 999 Relay_Log_File: db_slave-relay-bin.000002 Relay_Log_Pos: 883 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 999 Relay_Log_Space: 1183 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)
3267
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
