linux 系统维护及程序管理

[root@node2 data]# /opt/app/mysql4/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.27-hi4

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

root@localhost:mysql.sock[(none)] 11:55:18>use mysql;
Database changed
root@localhost:mysql.sock[mysql] 11:55:27>\q
Bye

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 128
table_cache = 512
key_buffer = 768M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
myisam_recover = force,backup
myisam_sort_buffer_size=128M
skip-innodb

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 256
table_cache = 1024
key_buffer = 1500M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
myisam_recover = force,backup
myisam_sort_buffer_size=256M
skip-innodb

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 256
table_cache = 1024
key_buffer = 4000M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=64M
max_heap_table_size=64M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
myisam_recover = force,backup
myisam_sort_buffer_size=512M
skip-innodb

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.9.42                   --主服务器的IP地址。
                  Master_User: backup                      --连接主服务器上当前的user。
                  Master_Port: 3306                        --当前主服务器上的Mysql端口。
                Connect_Retry: 60                          --master-connect-retry选项的当前值。
              Master_Log_File: mysql-bin.000018            --I/O线程当前正在读取的主服务器二进制日志文件的名称。
          Read_Master_Log_Pos: 111897343                   --在当前的主服务器二进制日志中，I/O线程已经读取的位置。
               Relay_Log_File: localhost-relay-bin.000026  --SQL线程当前正在读取和执行的中继日志文件的名称。（建议自定义名称,原因后面。）
                Relay_Log_Pos: 88316978                    --在当前的中继日志中，SQL线程已读取和执行的位置
        Relay_Master_Log_File: mysql-bin.000009            --由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
             Slave_IO_Running: Yes                         --I/O线程是否被启动并成功地连接到主服务器上。（）        
            Slave_SQL_Running: Yes                         --SQL线程是否被启动。
              Replicate_Do_DB: dxmaildb,dxaddb             --replicate-do-db（需要复制数据库的列表）
          Replicate_Ignore_DB:                             --replicate-ignore-db（不需要复制数据库的列表）
           Replicate_Do_Table:                             --同步不同库中的表格式为（replicate-do-table=db_name.tbl_name）
       Replicate_Ignore_Table:                             --当然是不同步库中的表了
      Replicate_Wild_Do_Table:                             --解决跨库操作更新        
  Replicate_Wild_Ignore_Table:                             --同上        
                   Last_Errno: 0                           --最近被执行的查询返回的错误数量
                   Last_Error:                             --最近被执行的查询返回的错误消息
                 Skip_Counter: 0                           --使用的用于SQL_SLAVE_SKIP_COUNTER的值
          Exec_Master_Log_Pos: 88316833                    --来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置    
              Relay_Log_Space: 4570242461                  --所有原有的中继日志结合起来的总大小,单位是byte
              Until_Condition: None                        --在START SLAVE语句的UNTIL子句中指定的值(三种状态(master/Relay/None))
                Until_Log_File:                            --在START SLAVE语句的UNTIL子句中指定的值
                Until_Log_Pos: 0                           --在START SLAVE语句的UNTIL子句中指定的值
           Master_SSL_Allowed: No                          --如果允许对主服务器进行SSL连接，则值为Yes        
           Master_SSL_CA_File:                                   CA文件
           Master_SSL_CA_Path:                                   CA路径
              Master_SSL_Cert:                                   CA证书
            Master_SSL_Cipher:
               Master_SSL_Key:                                        
        Seconds_Behind_Master: 230063                      --从属服务器“落后”多少秒（一个时间指示）        
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;

long_query_time = 1
log-slow-queries = /var/youpath/slow.log
log-queries-not-using-indexes
long_query_time 是指执行超过多久的sql会被log下来，这里是1秒。
log-slow-queries 设置把日志写在那里，可以为空，系统会给一个缺省的文件host_name-slow.log，我生成的log就在mysql的data目录
log-queries-not-using-indexes 就是字面意思，log下来没有使用索引的query。

/path/to/mysqldumpslow --help 看一下
-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string
-s，是order的顺序,主要有c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时后,解释为平均时间,但是用下来是倒叙方式

mysql > show variables like ’max_connections‘;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+