A、mysql 主服务器 名称:abao68 IP 地址:192.168.1.68 数据库版本为:mysql5.1.72
[root@CentOS67-68 ~]# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for RedHat-linux-gnu (x86_64) using readline 5.1
[root@centos67-68 ~]# uname -a
Linux centos67-68 2.6.32-642.15.1.el6.x86_64 #1 SMP Fri Feb 24 14:31:22 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
B、mysql 从服务器 名称:abao67 IP 地址:192.168.1.67 数据库版本为:mysql5.5.32
[root@abao67 ~]# mysql -V
mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1
[root@abao67 ~]# uname -a
Linux abao67 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
- [root@centos67-68 ~]# mysql -V
- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
- [root@centos67-68 ~]# mysql
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 2
- Server version: 5.1.73 Source distribution
- Copyright (c) 2000, 2013, Oracleand/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | test |
- +--------------------+
- 3 rows in set (0.00 sec)
- mysql> create database abaotest;
- Query OK, 1 row affected (0.00 sec)
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | abaotest |
- | mysql |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
方法一:
- [root@centos67-68 ~]# mysql -uroot -p abaotest < book_utf8.sql
- Enter password:
方法二:
- mysql> user abaotest;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user abaotest' at line 1
- mysql> use abaotest
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> source /root/book_utf8.sql
查看刚导入的数据表情况:
- mysql> show tables;
- +--------------------+
- | Tables_in_abaotest |
- +--------------------+
- | books |
- | category |
- +--------------------+
- 2 rows in set (0.00 sec)
- mysql> select * from category;
- +---------+---------------+
- | bTypeId | bTypeName |
- +---------+---------------+
- | 1 | windows应用 |
- | 2 | 网站 |
- | 3 | 3D动画 |
- | 4 | linux学习 |
- | 5 | Delphi学习 |
- | 6 | 黑客 |
- | 7 | 网络技术 |
- | 8 | 安全 |
- | 9 | 平面 |
- | 10 | AutoCAD技术 |
- +---------+---------------+
- 10 rows in set (0.00 sec)
知识扩展:在 centos6.7 下使用 yum 安装数据库之后需要改变 MySQL 数据库的默认编码
- 查看数据库默认编码
- mysql> status;
- --------------
- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
- Connection id: 2
- Current database: abaotest
- Current user: root@localhost
- SSL: Not in use
- Current pager: stdout
- Using outfile: ''
- Using delimiter: ;
- Server version: 5.1.73 Source distribution
- Protocol version: 10
- Connection: Localhost via UNIX socket
- Server characterset: latin1
- Db characterset: utf8
- Client characterset: latin1
- Conn. characterset: latin1
- UNIX socket: /var/lib/mysql/mysql.sock
- Uptime: 6 min 33 sec
- Threads: 1 Questions: 61 Slow queries: 0 Opens: 22 Flush tables: 1 Open tables: 9 Queries per second avg: 0.155
- 安装MySQL数据库时的默认编码是latin1,实际使用时可能要使用其他编码。下文描述如何将默认编码改为utf8:
- 首先修改MySQL的配置文件/etc/my.cnf:
- 在[client]下追加:
- default-character-set=utf8
- 在[mysqld]下追加:
- character-set-server=utf8
- 在[mysql]下追加:
- default-character-set=utf8
- 修改完毕后,使用如下命令之一重启mysql服务:
- service mysql restart
- /etc/init.d/mysql restart
- 改变后,之前手工创建的数据库如未显式指定编码,则其编码仍是默认的latin1,可使用如下命令更改编码:
- mysql> alter database db_name CHARACTER SET utf8;
[root@centos67-68 ~]# service mysqld stop
停止 mysqld: [确定]
修改 my.cnf 文件并在 [mysqld] 选项中加入以下内容:
[root@centos67-68 ~]# vi /etc/my.cnf
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- # Disabling symbolic-links is recommended to prevent assorted security risks
- character-set-server=utf8
- symbolic-links=0
- #---------以下是在原配置文件中添加的内容---------
- #启用二进制日志,默认存在/var/lib/mysql 下面
- log-bin=mysqllog
- #本机数据库ID 标示。其中master_id必须为1到232之间的一个正整数值
- server-id=1
- #��以被从服务器复制的库。二进制需要同步的数据库名abaotest
- binlog-do-db=abaotest
- #不可以被从服务器复制的库mysql
- # binlog-ignore-db=mysql
- #---------以上是在原配置文件中添加的内容---------
- [mysql]
- default-character-set=utf8
- [client]
- default-character-set=utf8
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
[root@centos67-68 ~]# service mysqld restart
停止 mysqld: [确定]
正在启动 mysqld: [确定]
- mysql > grant replication slave on * . * to slave@192.168.1.67 identified by "123456";
- Query OK,
- 0 rows affected (0.00 sec)
- [root@centos67-68 ~]# ls /var/lib/mysql/
- abaotest ibdata1 ib_logfile0 ib_logfile1 mysql mysqllog.000001 mysqllog.index mysql.sock test
- [root@centos67-68 ~]# mysql
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 3
- Server version: 5.1.73-log Source distribution
- Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> show master status;
- +-----------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +-----------------+----------+--------------+------------------+
- | mysqllog.000001 | 256 | abaotest | |
- +-----------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- mysql> show binlog events \G
- *************************** 1. row ***************************
- Log_name: mysqllog.000001
- Pos: 4
- Event_type: Format_desc
- Server_id: 1
- End_log_pos: 106
- Info: Server ver: 5.1.73-log, Binlog ver: 4
- *************************** 2. row ***************************
- Log_name: mysqllog.000001
- Pos: 106
- Event_type: Query
- Server_id: 1
- End_log_pos: 256
- Info: grant replication slave on *.* to slave@192.168.1.67 identified by "123456"
- 2 rows in set (0.00 sec)
- [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql
- Enter password:
- mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
- [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql
- Enter password:
- -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
- [root@centos67-68 ~]# ls
- all.sql anaconda-ks.cfg book_utf8.sql install.log install.log.syslog
- [root@centos67-68 ~]# rm -rf all.sql
- [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql
- Enter password:
- -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
- [root@centos67-68 ~]# scp all.sql 192.168.1.67:/root
- The authenticity of host '192.168.1.67 (192.168.1.67)' can't be established.
- RSA key fingerprint is c8:62:61:31:24:fa:77:44:da:be:26:d8:85:80:95:3a.
- Are you sure you want to continue connecting (yes/no)? yest^H
- Warning: Permanently added '192.168.1.67' (RSA) to the list of known hosts.
- root@192.168.1.67's password:
- all.sql 100% 521KB 521.3KB/s 00:00
- [root@abao67 ~]# mysql -u slave -h192.168.1.68 -p123456
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 7
- Server version: 5.1.73-log Source distribution
- Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | test |
- +--------------------+
- 2 rows in set (0.01 sec)
测试可以正常连接,但是没有发现 abaotest 数据库。
- [root@abao67 ~]# mysql -u root -p12345 < all.sql
- [root@abao67 ~]# mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 6
- Server version: 5.5.32-log Source distribution
- Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | abaotest |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 5 rows in set (0.00 sec)
- mysql> use abaotest
- Database changed
- mysql> select * from category;
- +---------+---------------+
- | bTypeId | bTypeName |
- +---------+---------------+
- | 1 | windows应用 |
- | 2 | 网站 |
- | 3 | 3D动画 |
- | 4 | linux学习 |
- | 5 | Delphi学习 |
- | 6 | 黑客 |
- | 7 | 网络技术 |
- | 8 | 安全 |
- | 9 | 平面 |
- | 10 | AutoCAD技术 |
- +---------+---------------+
- 10 rows in set (0.00 sec)
配置 MySQL 从服务器的 my.cnf 文件
vi /etc/my.cnf #编辑配置文件,在 [mysqld] 部分添加下面内容
server-id=2 #配置文件中已经有一行 server-id=1,修改其值为 2,表示为从数据库
log-bin=mysql-bin #启动 MySQ 二进制日志系统,注意:如果原来的配置文件中已经有这一行,就不用再添加了。
replicate-do-db=osyunweidb #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
replicate-ignore-db=mysql #不同步 mysql 系统数据库
:wq! #保存退出
注意:MySQL 5.1.7 版本之后,已经不支持把 master 配置属性写入 my.cnf 配置文件中了,只需要把同步的数据库和要忽略的数据库写入即可。
- mysql> slave stop;
- Query OK, 0 rows affected (0.00 sec)
- mysql> change master to master_host='192.168.1.68',master_user='slave',master_password='123456',master_log_file='mysqllog.000001',master_log_pos=256;
- Query OK, 0 rows affected (0.04 sec)
- mysql> start slave;
- Query OK, 0 rows affected (0.00 sec)
- mysql> SHOW SLAVE STATUS\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.1.68
- Master_User: slave
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysqllog.000001
- Read_Master_Log_Pos: 256
- Relay_Log_File: abao67-relay-bin.000002
- Relay_Log_Pos: 251
- Relay_Master_Log_File: mysqllog.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB: abaotest
- Replicate_Ignore_DB: mysql
- 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: 256
- Relay_Log_Space: 408
- 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
- 1 row in set (0.00 sec)
注意查看:Slave_IO_Running: YesSlave_SQL_Running: Yes 以上这两个参数的值为 Yes,即说明配置成功!
- 1、进入MySQL主服务器abao68
- #进入主服务器MySQL控制台
- mysql -u root -p
- #进入数据库
- mysql> use abaotest
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- #创建test表
- mysql> CREATE TABLE test ( id int not null primary key,name char(20) );
- Query OK, 0 rows affected (0.02 sec)
- 2、进入MySQL从服务abao67
- #进入MySQL控制台
- mysql -u root -p
- #进入数据库
- mysql> use abaotest
- #查看abaotest表结构,会看到有一个新建的表test,表示数据库同步成功
- mysql> use abaotest
- Database changed
- mysql> show tables;
- +--------------------+
- | Tables_in_abaotest |
- +--------------------+
- | books |
- | category |
- | test |
- +--------------------+
- 3 rows in set (0.00 sec)
您若能看到如上内容,证明您成功啦!
来源: http://www.linuxidc.com/Linux/2017-04/142770.htm