mysql 使用小结

一:mysqldump 导出数据

 

mysqldump -uroot --opt database <table> > bakup.sql
1:如果不写table表示导出database中所有表

2:bakup.sql中包含所有DDL语句和数据

3:如果只想要DDL语句,可以用--no-data
mysqldump -uroot --opt --no-data database >bakup.sql

4:如果只想要数据,可以用
mysqldump -uroot --opt -t database >bakup.sql 

5.加条件过滤
mysqldump -u -p --opt db table --where="xxx=xxx"

 

二:导入数据

 

mysql -uroot database < bakup.sql
bakup.sql 包含DDL语句和Insert语句

 

三:my.cnf(my.ini)的配置

以下是Mysql数据库服务器配置文件/etc/my.cnf的详细配置。应用场合是InnoDB引擎，4核CPU， 32位SUSE。

[client]
　　port        = 3306
　　socket      = /tmp/mysql.sock
　　# The MySQL server
[mysqld]
　　bind-address = 192.168.0.11
　　port         = 3306
　　socket       = /tmp/mysql.sock

 　 #设置表的默认类型。和default-storage-engine同义
　　default-table-type = innodb

　　#以下是网络传输配置
　　#连接进程帧听队列的大小
　　back_log = 50

　　#之前有过经验，connections超过370的时候，线程的栈空间被占满，mysqld无法工作，所以限制为200
　　max_connections=200

　　#连接500次发生错误之后，屏蔽从该host的所有连接
　　max_connect_errors = 500

　　#通信缓冲区的最大长度。包或任何生成的/中间字符串的最大大小。
　　max_allowed_packet = 32M

　　#在查询之间将通信缓冲区重设为该值。如果语句超出该长度，缓冲区自动扩大，直到max_allowed_packet字节。
　　net_buffer_length = 8K

　　#以下是查询优化
　　#避免外部锁定(默认是ON). 对应variable的skip_external_locking
　　skip-locking
　
  　#索引缓冲区(仅作用于MYISAM表和临时表)，决定了数据库索引处理的速度
　　key_buffer_size = 10M

　　#所有线程打开的表的数目(一个表使用2个文件描述符)，表数量多，就要大一些。
    #增大该值可以增加mysqld需要的文件描述符的数量。
    #可以检查Opened_tables(已经打开的表的数量)状态变量来检查你是否需要增加表缓存。
　　table_cache = 2048

　　#每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作
　　sort_buffer_size = 5M

　　#用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)。
　　#一般情况获得快速联接的最好方法是添加索引。
    #当增加索引时不可能通过增加join_buffer_size值来获得快速完全联接。
　　#将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接，需要多联接缓冲区。
　　join_buffer_size = 8M

　　#服务器应缓存多少线程以便重新使用。
     #当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程被放入缓存。
　　#如果状态变量Threads_created(创建用来处理连接的线程数)较大，你可能要增加thread_cache_size值。
     #缓存访问率的计算方法Threads_created/Connections(试图连接到(不管是否成功)MySQL服务器的连接数)
　　thread_cache_size = 8

　　#使应用程序向线程系统提供需要同时运行的期望的线程数目的提示
　　thread_concurrency = 8

　　#为缓存查询结果分配的内存的数量，加快查询的速度。默认值是0，即禁用查询缓存。
　　query_cache_size = 64M

　　#不要缓存大于该值的结果，默认值是1048576(1MB)。
　　query_cache_limit = 2M

　　#ft_min_word_len和ft_max_word_len表示FULLTEXT索引的最小和最大字长
　　ft_min_word_len = 4

　　#每个线程的堆栈大小
　　thread_stack = 192K

　　#事务隔离级别。
    #READ-UNCOMMITTED: 未提交读会出现脏读、不可重复读、幻读 ( 隔离级别最低，并发性能高 ) ；
    #READ-COMMITTED: 提交读会出现不可重复读、幻读问题（锁定正在读取的行）；
    #REPEATABLE-READ: 默认值，可重复读会出幻读（锁定所读取的所有行）；
    #SERIALIZABLE: 序列化保证所有的情况不会发生（锁表）
　　transaction_isolation = REPEATABLE-READ

　　#联合查询或者view的时候，可能会建立临时表，
     #如果在磁盘上建的话，会导致性能低，设置此值可以一定程度上加快速度
　　tmp_table_size = 64M

　　#每个线程连续扫描时为扫描的每个表分配的缓冲区的大小,如果进行多次连续扫描，可能需要增加该值
　　read_buffer_size = 1M

　　#当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。
     #将该变量设置为较大的值可以大大改进ORDER BY的性能。
     # 但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。
     #相反，只为需要运行大查询的客户端更改会话变量。
　　read_rnd_buffer_size = 2M

　　#以下是主从同步配置
　　# Replication Master Server (default)
　　#主机是1。备机从2开始。
　　server-id    = 1

　　#启动数据库更新二进制日志记录，日志文件名前缀为mysql-bin
　　log-bin=mysql-bin

　　#在事务过程中容纳二进制日志SQL语句的缓存大小，
　　#二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(–log-bin选项)的前提下为每个客户端分配的内存。
　　#如果经常使用大的，多语句事务，可以增加该值以获得更有的性能
　　binlog_cache_size = 5M

　　#是否记录慢查询，默认OFF。用long_query_time变量的值来确定“慢查询”。
　　log_slow_queries

　　#超过2S的query将记slow query日志
　　long_query_time = 2

　　#没有使用索引的查询也作为慢速查询记录到慢速日志里
　　#或者用log-queries-not-using-indexes
　　log_long_format

　　#告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，不应将更新保存到二进制日志中。
　　binlog-ignore-db = information_schema
　　binlog-ignore-db = cluster
　　binlog-ignore-db = mysql

　　#告诉从服务器限制默认数据库(由USE所选择)为db_name的语句的复制
　　replicate-do-db = test1
　　replicate-do-db = test2

　　#跨库更新表要用到如下配置。告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。
     #模式可以包含‘%’和‘_’通配符，与LIKE模式匹配操作符具有相同的含义。
     #要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。
　　replicate-wild-do-table = test1.%
　　replicate-wild-do-table = test2.%

　　#当Slave从Master数据库读取日志时更新新写入日志中。
     #如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新
　　log-slave-updates

　　#以下是InnoDB引擎配置
　　#InnoDB 不会自己建立目录，必须自己使用操作系统命令建立相应的目录。
     #检查你的 MySQL 服务程序在 datadir 目录里有足够的权限建立文件(mysql用户组拥有)。
　　#这是InnoDB表的目录共用设置。如果没有设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。
     #如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。
　　innodb_data_home_dir = /data/dbdata/

　　#单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。
     #文件大小以 MB 单位指定。因此在文件大小指定后必有“M”。 
     #InnoDB 也支持缩写"G",1G=1024M.从3.23.44 开始,在那些支持大文件的操作系统上可以设置数据文件大小大于4GB
     #而在另一些操作系统上数据文件必须小于2GB
　　#如果用 autoextend 选项描述最后一个数据文件,当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件,每次增量为8MB
　　#InnoDB是不会注意你的OS文件尺寸限制的,在一些文件系统中你可能要设定最大容量2G限制：
　　#innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
　　#这里应当预先创建好10个2048M的文件，目前还没有创建
　　innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M;ibdata9:2000M;ibdata10:2000M:autoextend

　　#InnoDB 日志文件的路径。如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的ib_logfile0和ib_logfile1文件。
　　#将日志文件与数据文件分别放在不同的物理硬盘中对提高性能通常是很有益的
　　innodb_log_group_home_dir = /data/dbdata/

　　#主要用来存储表结构和数据字典，表越多要求内存就越大
　　innodb_additional_mem_pool_size = 16M

　　# You can set .._buffer_pool_size up to 50 – 80 %
　　# of RAM but beware of setting memory usage too high
　　#表和索引数据的内存缓冲区，越大性能越高。但不能超过物理内存的50%。若64位OS，该值可以更大
　　#32位OS受2G内存的限制，不能将内存使用设置太高， glibc 会把进程堆增长到线程堆栈之上，这将会使服务器崩溃。下面的接近或超过于 2G 将会很危险：
　　#innodb_buffer_pool_size+key_buffer+max_connections*(sort_buffer_size+record_buffer_size+binlog_cache_size)+max_connections*2 MB
　　#每个线程将使用 2MB(MySQL AB 二进制版本为 256 KB)的堆栈，在最坏的环境下还会使用 sort_buffer_size + record_buffer_size 的附加内存。
　　innodb_buffer_pool_size = 1G

　　#InnoDB 中的文件 I/O 线程。通常设置为 4
　　innodb_file_io_threads = 4

　　#并发线程。2*(内核数量+磁盘数量)
　　innodb_thread_concurrency = 16

　　#1表示每次事务结束都写日志并刷新磁盘；2表示每次事务写日志但不刷新磁盘(每秒刷新)；0(默认值)表示每秒写日志并刷新磁盘。0表示最多丢失1秒的数据，但性能最好。
　　innodb_flush_log_at_trx_commit = 0

　　#InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。因此，如果有大的事务，设置大的日志缓冲可以减少磁盘I/O。
　　innodb_log_buffer_size = 8M

　　# Set .._log_file_size to 25 % of buffer pool size
　　#日志文件大小设置。增大该值可减少刷新日志频率，但会延长mysql启动和灾难恢复的时间。
　　#日志组中的每个日志文件的大小(单位 MB)。
     #如果 n 是日志组(innodb_log_files_in_group)中日志文件的数目，
     #那么理想的数值为 1M 至缓冲池(innodb_log_buffer_size)大小的 1/n。较大的值，
     #可以减少刷新缓冲池的次数，从而减少磁盘 I/O。
     #但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。
　　innodb_log_file_size = 256M

　　#日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用
　　innodb_log_files_in_group = 3

　　#InnoDB行锁导致的死锁等待时间(默认值是50S)
　　innodb_lock_wait_timeout = 30

　　#是否支持分布式事务，关闭以提高性能(默认是ON)
　　innodb_support_xa = OFF
　
[mysqldump]
　　quick
　　max_allowed_packet = 16M
　　
[mysql]
　　no-auto-rehash
　　# Remove the next comment character if you are not familiar with SQL
　　#safe-updates
　　
[isamchk]
　　key_buffer = 20M
　　sort_buffer_size = 20M
　　read_buffer = 2M
　　write_buffer = 2M
　　
[myisamchk]
　　key_buffer = 20M
　　sort_buffer_size = 20M
　　read_buffer = 2M
　　write_buffer = 2M
　　
[mysqlhotcopy]
　　interactive-timeout