Home | Mirror | Search

第 1 章 MySQL Server

目錄

1. MySQL Installation
1.1. Installation by apt-get under debian/ubuntu
1.2. Installation by source code
1.3. MySQL binary distribution
1.4. mysql-5.5.21-debian6.0-i686.deb
1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)
1.6. mysql-admin
2. my.cnf
2.1. bind-address
2.2. Configuring Database Character Encoding
2.3. max_connections
2.4. storage-engine
2.5. max_allowed_packet
2.6. skip-name-resolve
2.7. 禁用TCP/IP連結
2.8. timeout
2.9. Example for my.cnf
3. mysql log
4. Security
5. Replication
5.1. Master Slave
5.1.1. Master
5.1.2. Slave
5.1.3. Testing
5.1.4. 將現有資料庫遷移到主從結構資料庫
5.1.5. 主從複製安全問題
5.1.6. 主從不同步問題
5.2. Master Master(主主)
5.2.1. Master A
5.2.2. Master B
5.2.3. 將Master A 資料庫 同步到 Master B 兩端資料庫內容保持一致
5.2.4. Master A - B 同步兩端資料庫
5.2.5. Master A 資料庫解除只讀權限
5.2.6. 查看主主的工作狀態
5.3. 與複製有關的問題
5.3.1. expire-logs-days
5.3.2. Semisynchronous Replication
6. MySQL Custer
6.1. Management node (MGM node)
6.2. Data node
6.3. SQL node
6.4. Starting
6.5. Shutdown
6.6. Testing
7. Monitoring
7.1. Analysis and Optimization
7.1.1. mytop - top like query monitor for MySQL
7.1.2. mtop - MySQL terminal based query monitor
7.1.3. innotop
7.1.4. mysqlreport
7.2. Munin
7.3. Cacti
7.4. Monitoring MySQL with SNMP
8. Import / Export (Backup and Recovery)
8.1. Export(Backup)
8.2. Import(Recovery)
8.3. xml
8.4. 備份表數據
8.5. source
8.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
8.7. AutoMySQLBackup
8.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
8.8.1. Percona yum Repository
8.8.2. Creating an Incremental Backup
9. Snapshot Backup

1. MySQL Installation

http://downloads.mysql.com/archives.php

1.1. Installation by apt-get under debian/ubuntu

安裝環境 ubuntu 8.10

sudo apt-get install mysql-server
		

New password for the MySQL "root" user

		
         ┌──────────────────────┤ Configuring mysql-server-5.0 ├─────────────────────┐
         │ While not mandatory, it is highly recommended that you set a password for the MySQL administrative "root" user.  │
         │                                                                                                                  │
         │ If that field is left blank, the password will not be changed.                                                   │
         │                                                                                                                  │
         │ New password for the MySQL "root" user:                                                                          │
         │                                                                                                                  │
         │ ****____________________________________________________________________________________________________________ │
         │                                                                                                                  │
         │                                                      <Ok>                                                        │
         │                                                                                                                  │
         └─────────────────────────────────────────────────────────────┘
		
		

Repeat password for the MySQL "root" user

		
         ┌───┤ Configuring mysql-server-5.0 ├────┐
         │                                             │
         │                                             │
         │ Repeat password for the MySQL "root" user:  │
         │                                             │
         │ ****_______________________________________ │
         │                                             │
         │                   <Ok>                      │
         │                                             │
         └─────────────────────────┘
         
		

create database

		
create database example;

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C6325DAF39AE6CC34E960D3C65F1398FE467E1D0' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GRANT ALL PRIVILEGES ON example.* TO 'dbuser'@'localhost' IDENTIFIED BY '******' WITH GRANT OPTION;
FLUSH PRIVILEGES;

./mysql -udbuser -p
Enter password:

./mysql -udbuser -p example < /tmp/example_china_copy.sql

./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use example;
Database changed
mysql> show tables;
		
		

配置檔案樣本

debian:~# ls /usr/share/doc/mysql-server-5.0/examples/
my-huge.cnf.gz  my-innodb-heavy-4G.cnf.gz  my-large.cnf.gz  my-medium.cnf.gz  my-small.cnf  ndb_mgmd.cnf
		

1.2. Installation by source code

		
./configure \
--prefix=/usr/local/$MYSQL_DIR \
--enable-assembler \
--enable-local-infile \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=none \
--with-openssl \
--with-pthread \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-mysqld-user=mysql \
--with-mysqld-ldflags \
--with-client-ldflags \
--with-comment \
--with-big-tables \
--without-ndb-debug \
--without-docs \
--without-debug \
--without-bench

make && make install
		
		

/usr/local/$MYSQL_DIR/bin/mysql_install_db

other option

--without-isam
--without-innodb
--without-ndbcluster
--without-blackhole
--without-ibmdb2i
--without-federated
--without-example
--without-comment
--localstatedir=/usr/local/mysql/data
		

1.3. MySQL binary distribution

		
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
		
		

install core database

		
[root@test mysql]# ./scripts/mysql_install_db
Installing MySQL system tables...
100428 23:16:20 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
100428 23:16:20 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h db.example.com password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!
		
		

set root's password

[root@test mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@test mysql]# /etc/init.d/mysqld start
Starting MySQL.                                            [  OK  ]

[root@test mysql]# ./bin/mysqladmin -u root password 'chen'
[root@test mysql]# ./bin/mysqladmin -u root -h db.example.com password 'chen'
		

test

		
[root@test mysql]# ./bin/mysql -uroot -pchen
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.45 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
		
		

1.4. mysql-5.5.21-debian6.0-i686.deb

		
sudo apt-get install libaio1


sudo groupadd mysql
sudo useradd -r -g mysql mysql

sudo dpkg -i mysql-5.5.21-debian6.0-i686.deb

cd /opt/mysql/
sudo chown -R mysql .
sudo chgrp -R mysql .

cd server-5.5/

sudo support-files/binary-configure

sudo chown -R mysql data

# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf

shell> bin/mysqld_safe --user=mysql &

# Next command is optional
sudo cp support-files/mysql.server /etc/init.d/mysql
		
		

1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)

準備下面的軟件包

# ls -1
MySQL-client-5.5.25-1.el6.x86_64.rpm
MySQL-devel-5.5.25-1.el6.x86_64.rpm
MySQL-server-5.5.25-1.el6.x86_64.rpm
MySQL-shared-5.5.25-1.el6.x86_64.rpm
MySQL-shared-compat-5.5.25-1.el6.x86_64.rpm
		

使用 yum 本地安裝 rpm, yum 可以幫你解決依賴于衝突

# yum localinstall MySQL-*
		
# /etc/init.d/mysql start
Starting MySQL... SUCCESS!

# /usr/bin/mysqladmin -u root password 'tUG26WSslP30bkbwtMhn'
		

1.6. mysql-admin

$ sudo apt-get install mysql-admin
		

運行mysql-admin

/usr/bin/mysql-admin
		

運行 mysql-query-browser

mysql-query-browser --query="SELECT * FROM users"
		
comments powered by Disqus