mysql学习笔记-服务器端程序

来源：互联网发布：mac无线鼠标没反应编辑：程序博客网时间：2024/06/03 19:16

*MySQL服务器的启动

1. mysqld: mysql服务器，它用来管理mysql的data directory，data directory包含数据库和表。data directory也是其他信息的默认存放位置，比如log files和status files.

当mysqld启动时，他监听来自客户端的网络连接并代理客户端来访问数据库。

mysqld有一些系统变量会影响它在运行时的行为。这些系统变量可以在启动时设置，也可以在运行时修改。

mysqld还有一些状态变量来提供他的行为。可以通过查看这些状态变量来了解运行时的行为特性。

2. mysqld_safe: linux平台上用来启动mysql服务器的推荐方式。它可以在服务器出错时重启服务器，记录运行时信息到错误日志。

mysqld_safe命令行上指定的参数选项会传递个mysqld.但是在配置文件的[mysqld_safe]组中的配置不会传递给mysqld.

mysqld_safe会读取配置文件中的[mysqld][server][mysqld_safe]部分的配置。

FormatDescription--basedir=pathPath to MySQL installation directory--core-file-size=sizeSize of core file that mysqld should be able to create--datadir=pathPath to data directory--defaults-extra-file=file_nameRead option file in addition to usual option files--defaults-file=file_nameRead only named option file--helpDisplay help message and exit--ledir=pathPath to directory where server is located--log-error=file_nameWrite error log to named file--malloc-lib=[lib-name]Alternative malloc library to use for mysqld--mysqld=prog_nameName of server program to start (in ledir directory)--mysqld-version=suffixSuffix for server program name--nice=priorityUse nice program to set server scheduling priority--no-defaultsRead no option files--open-files-limit=countNumber of files that mysqld should be able to open--pid-file=file_namePath name of process ID file--plugin-dir=pathDirectory where plugins are installed--port=numberPort number on which to listen for TCP/IP connections--skip-kill-mysqldDo not try to kill stray mysqld processes--skip-syslogDo not write error messages to syslog; use error log file--socket=pathSocket file on which to listen for Unix socket connections--syslogWrite error messages to syslog--syslog-tag=tagTag suffix for messages written to syslog--timezone=timezoneSet TZ time zone environment variable to named value--user={user_name|user_id}Run mysqld as user having name user_name or numeric user ID user_id

[root@vmhostd bin]# mysqld_safe --no-defaults --basedir=/usr/local/mysql --ledir=/usr/local/mysql/bin --log-error=/tmp/my.err --mysqld=mysqld --port=3340

[root@vmhostd bin]#/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/vmhostd.net.err --pid-file=/usr/local/mysql/data/vmhostd.net.pid --socket=/tmp/mysql.sock --port=3306

[root@vmhostd bin]#mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/vmhostd.net.pid

3. mysql.server

to start/stop mysql server in System V-style directories, it uses the configuration in [mysql.server][mysqld] section of configuration file.

4. mysqld_multi:

用来管理在不同socket file和端口上监听的多个mysqld进程，用来启动停止服务，报告当前状态，使用[mysqldN]部分的配置。

[root@vmhostd bin]#mysqld_multi start 17

[root@vmhostd bin]#mysqld_multi stop 8,10-13

[root@vmhostd bin]#mysqld_multi --example

×查看当前的连接数，状态等信息

mysql> show processlist;
mysql> show status like 'Open%';

mysql> show status like 'Thread%';

mysql> show variables like '%char%';

×MySQL客户端mysql

[root@vmhostd bin]#mysql --defaults-file=/etc/my.cnf -uscott -pscott -hvmhostd.net scott

#mysqladmin -uroot -p ping //检查服务器是否可用

#mysqladmin -uroot -p password //修改root用户的密码

#mysqladmin -uroot -p shutdown //关闭服务器

# mysqladmin -uroot -proot variables//查看系统变量

# mysqladmin -uroot -proot status //查看状态

×mysqlcheck客户端

root@vmhostd tmp]# mysqlcheck -uroot -proot --optimize scott depart

root@vmhostd tmp]# mysqlcheck -uroot -proot --analyze scott depart

root@vmhostd tmp]# mysqlcheck -uroot -proot --repair scott depart

root@vmhostd tmp]# mysqlcheck -uroot -proot --check scott depart

*mysqldump:数据库逻辑备份

mysqldump可以使用[client]和[mysqldump]部分的配置

[root@vmhostd dump]# mysqldump -uroot -proot scott depart -w 'did < 10' --lock-all-tables>scott.depart.sql //到处scott.depart中id小于10的记录到文件中

[root@vmhostd dump]# mysql -uroot -proot test<scott.depart.sql //还原数据到test数据库

[root@vmhostd dump]# mysqldump -uroot -proot --no-data scott -R>depart.sql //只备份scott表结构，到处存储过程函数。不导出数据

*mysqlimport导入数据

[root@vmhostd dump]# mysqlimport -uroot -proot --fields-enclosed-by=' --fields-terminated-by=, scott /usr/local/mysql/dump/employee.txt //导入数据到scott.employee中，数据以逗号分隔,单引号括起来

[root@vmhostd dump]# mysqlimport -uroot -proot -d --fields-terminated-by=, scott /usr/local/mysql/dump/employee.txt //导入前先删除表中的数据

*load data infile

mysql> load data infile '/usr/local/mysql/dump/employee.txt' into table scott.employeeFIELDS TERMINATED BY ','; //fields terminated by ',' should be added,otherwise will raise "Data truncated for colomn..." error

*mysqlshow 查询数据库信息，表信息，字段信息

[root@vmhostd dump]# mysqlshow -uroot -proot scott depart dname //查看scott数据库下表depart的dname字段

×mysqlslap压力测试

[root@vmhostu mysql]# mysqlslap -uscott -pscott -hvmhostd.net --delimiter=";" --create-schema="scott" --create="create table a(b int);insert into a values(23)" --query="select * from a" --concurrency=50 --iterations=200

0 0