Remote Technology Jobs

MySQL server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs. Example usage: mysqlbinlog mysql-bin.000212 --start-position=589388355 | mysql   --login-path=db5 mysqlbinlog binlog.0000088 --start-datetime="2015-08-08 18:08:08" | mysql --login-path=db5 --force

To get a list of active transactions and locks that are currently executing against our target database Simply use: SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys . innodb_lock_waits ; If a NULL value is reported for the blocking query, see Identifying a Blocking Query After the Issuing Session Becomes Idle .  For MySQL 8, use  performance_schema SELECT r . trx_id waiting_trx_id, r . trx_mysql_thread_id waiting_thread, r . trx_query waiting_query, b . trx_id blocking_trx_id, b . trx_mysql_thread_id blocking_thread, b . trx_query blocking_query FROM performance_schema . data_lock_waits w INNER JOIN information_schema . innodb_trx b ON b . trx_id = w . blocking_engine_transaction_id INNER JOIN information_schema . innodb_trx r ON r . trx_id = w . requesting_engine_transaction_id ; MySQL captures transaction and lock information in the INNODB_TRX, INNODB_LOCK...

Change MySQL Flag  default_time_zone to the value you want. For example, to change to Eastern Standard time: change default_time_zone to -05:00

Change MySQL Flag  default_time_zone to the value you want. For example, to change to Eastern Standard time: change default_time_zone to -05:00

If you want to delete many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful: Select the rows not to be deleted into an empty table that has the same structure as the original table: create table t_copy like t; insert into t_copy SELECT * FROM t WHERE ... ; See also: How to Copy Table in MySQL Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name: RENAME TABLE t TO t_old, t_copy TO t; Drop the original table: DROP TABLE t_old; Note that Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly. If you need a smaller table for development, to keep the foreign keys: create table contacts_new like contacts; insert into c...

If you want to delete many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful: Select the rows not to be deleted into an empty table that has the same structure as the original table: create table t_copy like t; insert into t_copy SELECT * FROM t WHERE ... ; See also: How to Copy Table in MySQL Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name: RENAME TABLE t TO t_old, t_copy TO t; Drop the original table: DROP TABLE t_old; Note that Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly. If you need a smaller table for development, to keep the foreign keys: create table contacts_new like contacts; insert into c...

Run: ssh - L 3306 : 127.0 . 0.1 : 3306 database@ goyun.info -L [bind_address:]port:host:hostport              Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side. Then you can access MySQL server remotely: mysql - h 127.0 . 0.1

The following codes are the same, they are just shown on different formats. Choose the one you like. ''' @author: it.goyun.info ''' import MySQLdb import time db = MySQLdb.connect(host= "192.168.1.19" , user= "i88ca" , passwd= " goyun.info " , db= " goyun.info " ) #create a cursor for the select cur = db.cursor() while ( True ): cur.execute( "show processlist;" ) for row in cur.fetchall() : # print row[0], row[1] print row[ 7 ] time.sleep( 8 ); # close the cursor cur.close() # close the connection db.close ()

Install monit from EPEL, first install EPEL : $ sudo rpm -Uvh  http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm $ sudo yum -y install monit in /etc/monit.conf # set daemon mode timeout to 1 minute set daemon 60 # Include all files from /etc/monit.d/ include /etc/monit.d/* check process mysql with pidfile /var/run/mysqld/mysqld.pid group database start program = "/etc/init.d/mysqld start" stop program = "/etc/init.d/mysqld stop" if failed host 127.0 . 0 . 1 port 3306 then restart if 5 restarts within 5 cycles then timeout # monitoring apache check process apache with pidfile /var/run/httpd/httpd.pid group www start program = "/etc/init.d/httpd start" stop program = "/etc/init.d/httpd stop" if failed host www.goyun.info port 80 protocol http then restart # the following is to check web site with content. check host www.goyun.info with address www.goyun.info if failed url...

For non-sleep processlist: $ mysql --login-path=root -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST pl where pl.command not in ('Sleep')\G "  | less For all processlist: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST SHOW FULL PROCESSLIST

The .mysql_history file is internally used by the command line editiing utility program, called libedit. The file is not intended to be directly viewed, or edited etc. The content of the file is encoded by wctomb. To view the content: shell> cat ~/.mysql_history | python2.7 -c "import sys; print(''.join([l.decode('unicode-escape') for l in sys.stdin]))" If your system has python 3.x installed, the command must be changed like below: shell> cat ~/.mysql_history | python -c "import sys; print(*[l.decode('unicode-escape') for l in sys.stdin.buffer])" MyCli History and Search MyCli keeps track of the queries entered in the repl. Up/Down arrow can be used to navigate the history. Pressing <C-r> will enable incremental history search. So press <C-r> and then start typing your search term to see the queries narrowed down. You can cycle through the matches by pressing <C-r> again. The history file ~/.mycli-history contains al...

To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. Examples: mysql --host=127.0.0.1 mysql --protocol=TCP You can also easily make that the default my editing your my.cnf so it has this ([client] means any client: [client] protocol=tcp

SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'goyun' AND COLUMN_KEY != '' For primary key: SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'goyun' AND COLUMN_KEY != 'PRI'

MySQL LOW_PRIORITY only affects storage engines which use table-level locking, like MyISAM and MEMORY. None of those engines support foreign key constraints.

Change innodb_lock_wait_timeout Property Value Command-Line Format --innodb-lock-wait-timeout=# System Variable innodb_lock_wait_timeout Scope Global, Session Dynamic Yes Type Integer Default Value 50 Minimum Value 1 Maximum Value 1073741824 The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

To solve this problem, we need to change max_allowed_packet=4096M ( or some other value suitable for you) in /etc/my.cnf file. To make sure we set it correctly, run select @@max_allowed_packet; If you unfortunately put more than 2 lines of "max_allowed_packet= " in the configuration file, the last one takes effect.

The system command works for you: Example: system ls -l

slow_query_log can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log.

To solve the problem: mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot  execute this statement when executing 'SELECT INTO OUTFILE' you can run SHOW VARIABLES LIKE "secure_file_priv"; and use the folder you are told there as the output folder in your mysqldump command. Example: mysql> SHOW VARIABLES LIKE "secure_file_priv"\G; *************************** 1. row *************************** Variable_name: secure_file_priv         Value: /var/lib/mysql-files/ 1 row in set (0.00 sec)