MySQL Problem summary

1, Slave_sql_running:no

Reason:

1. The program may have been written on the slave

2. It is also possible that the transaction rollback is caused by the slave machine being reset.

Solution One:

mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

Solution Two:

First stop the slave service: Slave stop
To view host status on the primary server:
Record the value corresponding to file and position

Enter Master

Mysql> Show master status;
+----------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 |              33622483 |                  | |
+----------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Then perform a manual synchronization on the slave server:

mysql> change master to 
> master_host=‘master_ip‘,
> master_user=‘user‘, 
> master_password=‘pwd‘, 
> master_port=3306, 
> master_log_file=localhost-bin.000094‘, 
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

Mysql> Show Slave Status\g
1. Row ***************************
........
master_log_file:localhost-bin.000094
read_master_log_pos:33768775
relay_log_file:localhost-relay-bin.000537
relay_log_pos:1094034
relay_master_log_file:localhost-bin.000094
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:

Manual sync needs to stop Master's write operation!

2, Slave failed to initialize relay log info structure from the repository

Add the following entry in the MY.CNF configuration file:

Relay_log =/opt/mysql/logs/mysql-relay-bin

Restart the MySQL instance for it to take effect.

3, ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error Log

Workaround:

1. Enter the default database storage directory for MySQL database:

/usr/local/mysql/var

This directory depends on the actual situation, the use of different installation methods, or compile the specified directory is different, this directory is not necessarily the same, but just find the database storage directory is good.

2. Delete the following two files:

Master.info

Relay-log.info

3. After logging into the database, execute:

Start slave;

Returns the following information:

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

This means that the problem has been solved.

4. Starting MySQL. The server quit without updating PID file

Workaround:

1. may be/usr/local/mysql/data/rekfan.pid file does not have permission to write
Workaround: Give permission, execute "chown-r mysql:mysql/var/data" "Chmod-r 755/usr/local/mysql/data" and restart mysqld!
2. The MySQL process may already exist in the process
WORKAROUND: Use the command "Ps-ef|grep mysqld" to see if there is a mysqld process, kill with "kill-9 process number" and then restart mysqld!
3. It may be the second time that MySQL is installed on the machine, with residual data affecting the start of the service.
Workaround: Go to MySQL data directory/data See, if there is mysql-bin.index, quickly delete it, it is the culprit.
4.mysql The/ETC/MY.CNF profile is used when the configuration file is not specified at startup, please open this file to see if there is a specified data directory (DATADIR) under the [Mysqld] section.
WORKAROUND: Please set this line under [mysqld]: DataDir =/usr/local/mysql/data
5.skip-federated Field Issues
Workaround: Check the/etc/my.cnf file for any skip-federated fields that have not been commented out, and if so, comment them out immediately.
6. Error log directory does not exist
Workaround: Use "Chown" "chmod" command to give MySQL owner and permissions
7.selinux, if it is a CentOS system, the default is to turn on SELinux
Workaround: Turn it off, open the/etc/selinux/config, change the selinux=enforcing to selinux=disabled, and then save the restart machine and try again.
But not resolved, and finally directly to the/usr/local/mysql/data directory chmod 777-r/usr/local/myql/data Problem resolution should be a permissions issue, cannot generate Localhost.localdomain.pid file
8. Modified the machine name, reported a starting MySQL. The server quit without updating PID file (/opt/mysql/data/xxx.pid).
XXX for the new machine name.
Enter the Mysql/data directory to find the. pid file found Local is Localhost.pid then renamed to xxx.pid boot OK

5. Configuration Files

The MySQL configuration file is my.cnf, and its location is set according to the installation.
When the Mysqld service starts, the configuration file is read in a certain order by default.
1
2
3
[Email protected] ~]#/opt/mysql/libexec/mysqld--verbose--help
Default options is read from the following files in the given order:
/ETC/MY.CNF/ETC/MYSQL/MY.CNF/OPT/MYSQL/ETC/MY.CNF ~/.my.cnf
As you can see, the system defaults to/ETC/MY.CNF-----/etc/mysql/my.cnf----/usr/local/mysql/ MY.CNF read the configuration file sequentially, when there are multiple profiles, MySQL will take the parameters in the last configuration file that is read to prevail.
The common configuration parameters are:
1
Port = 3306
MYSQLD Service Runtime port number, default is 3306
1
Socket =/tmp/mysql.sock
The socket file is unique to the Linux/unix system, where the client connection to the user can be connected without a TCP/IP network and directly using the socket file.
1
Back_log = 300
The value for the profile MySQL to temporarily stop responding to a new request, how many requests within a short period of time can exist in the stack, if the system has a lot of connections in a short period of time, should increase the value, it is best to set less than 512 integer
1
Skip-networking
Does not listen on the TCP/IP port, all connections are connected through a local socket file, which improves security and determines that the database cannot be connected over the network.
1
Skip-locking
Avoid external locking of MySQL for enhanced stability
1
Skip-name-resolve
To avoid the DNS resolution of external connections to MySQL, if you use this setting, then the remote host connection can only use IP, and cannot use the domain name
1
Max_connections = 3000
Specifies the maximum number of connection processes allowed by the MySQL service.
1
max_connect_errors = 1000
The number of times each host connection is allowed to break unexpectedly, when more than that number of times the MySQL server will disallow the host's connection request until the MySQL service restarts, or the flushhosts command empties the host's related information
1
Table_cache = 614k
The size of the table's buffer zone, when MySQL accesses a table, if the MySQL table buffer has space, then this table will be opened into the buffer zone, the advantage is that the content of the table can be accessed more quickly.
If the value of Open_tables and Opened_tables is close to this value, then the value should be increased by a longer size
1
Max_allowed_packet = 4M
Set the maximum number of messages that can be transmitted at one time in the network transmission, the system defaults to 1M, the maximum can be 1G
1
Sort_buffer_size = 16M
The sort buffer is used to handle the sort ordered by the GroupBy queue and the default size of 2M, which corresponds to the allocated memory is exclusive to each connection, if there are 100 connections, the actual allocated sort buffer size is 6*100; recommended setting is 6m-8m
1
Join_buffer_size 8M
The buffer size used by the union query operation.
1
Thread_cache_size = 64
Set the maximum number of connection threads that can be cached in the Threadcache pool, the default is 0, which means that the amount of thread stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache, and if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, then the thread will be recreated. Set the rule to: 1G memory set to 8,2G memory set to 16,4g above set to 64
1
Query_cache_size = 64M
Specify the size of the MySQL query buffer to buffer the results of the Select and return the results directly at the next time the same query is not executed, depending on the size of the qcache_lowmem_prunes to see if the current load is high enough
1
Query_cache_limit = 4M
Only results that are smaller than this value are buffered, placing a large result that overrides all other query results
1
Tmp_table_size 256M
The size of the memory temp table, and if this value is exceeded, the temporary table is written to disk
1
Default_storage_engine = MYISAM
Storage engine used by default when creating tables
1
Log-bin=mysql-bin
To turn on the binary logging feature
1
Key_buffer_size = 384M
Specifies the size of the index buffer and the memory is set to 256M or 384M at 4G time
1
Read_buffer_size = 8M
Buffer size for full table scan of MyISAM table
。。。。。。。。。
Common Configuration Instances
[Client]
Default-character-set = UTF8
Port = 3306
Socket =/tmp/mysql.sock
[Mysqld]
user = MySQL
Port = 3306
Socket =/tmp/mysql.sock
Basedir =/opt/mysql
DataDir =/opt/mysql/var
Log-error =/opt/mysql/var/mysql-error.log
Pid-file =/opt/mysql/var/mysql.pid
Log_slave_updates = 1
Log-bin =/opt/mysql/var/mysql-bin
Binlog_format = Mixed
Binlog_cache_size = 4M
Max_binlog_cache_size = 8M
Max_binlog_size = 1G
Expire_logs_days = 90
Key_buffer_size = 384M
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 16M
Join_buffer_size = 2M
Thread_cache_size = 8
Query_cache_size = 32M
Query_cache_limit = 2M
Query_cache_min_res_unit = 2k
Thread_concurrency = 32
Table_cache = 614
Table_open_cache = 512
Open_files_limit = 10240
Back_log = 600
Max_connections = 5000
Max_connect_errors = 6000
external-locking = FALSE
Max_allowed_packet = 16M
Default-storage-engine = MYISAM
Thread_stack = 192k
Transaction_isolation = read-committed
Tmp_table_size = 256M
Max_heap_table_size = 512M
Bulk_insert_buffer_size = 64M
Myisam_sort_buffer_size = 64M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1
Myisam_recover
Long_query_time = 2
Slow_query_log
Slow_query_log_file =/opt/mysql/var/slow.log
Skip-name-resolve
Skip-locking
Skip-networking
Innodb_additional_mem_pool_size = 16M
Innodb_buffer_pool_size = 512M
Innodb_data_file_path = Ibdata1:256m:autoextend
Innodb_file_io_threads = 4
Innodb_thread_aoncurrency = 8
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 16M
Innodb_log_file_size = 128M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[Mysqldump]
Quick
Max_allow_packet = 64M
[MySQL]
No-auto-rehash
Safr-updates
[Myisamchk]
Key_buffer_size = 256M
Sort_buffer_size = 256M
Read_buffer = 2M
[Mysqldump]
Quick
Max_allow_packet = 64M
[MySQL]
No-auto-rehash
Safe-updates
[Myisamchk]
Key_buffer_size = 256M
Sort_buffer_size = 256M
Read_buffer = 2M
Write_buffer = 2M
[Mysqlhotcopy]
Interactive-timeout

MySQL Problem summary