You can log every query to a log file really easily: mysql> SHOW VARIABLES LIKE “general_log%”; +——————+—————————-+ | Variable_name | Value | +——————+—————————-+ | general_log | OFF | | general_log_file | /var/run/mysqld/mysqld.log | +——————+—————————-+ mysql> SET GLOBAL general_log = ‘ON’; Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log Then don’t forget to … Read more
An INT will always be 4 bytes no matter what length is specified. TINYINT = 1 byte (8 bit) SMALLINT = 2 bytes (16 bit) MEDIUMINT = 3 bytes (24 bit) INT = 4 bytes (32 bit) BIGINT = 8 bytes (64 bit). The length just specifies how many characters to pad when selecting data … Read more
You can do with the –no-data option with mysqldump command mysqldump -h yourhostnameorIP -u root -p –no-data dbname > schema.sql
UPDATE your_table SET your_field = REPLACE(your_field, ‘articles/updates/’, ‘articles/news/’) WHERE your_field LIKE ‘%articles/updates/%’ Now rows that were like http://www.example.com/articles/updates/43 will be http://www.example.com/articles/news/43 How to find and replace text in a MySQL database
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE tablename MODIFY columnname INTEGER; This will change the datatype of given column Depending on how many columns you wish to modify it might be best to generate a script, or use some kind of mysql client GUI
you can change the encryption of the password like this. ALTER USER ‘yourusername’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘youpassword’;
Execute the following query in MYSQL Workbench ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’; Where root as your user localhost as your URL and password as your password Then run this query to refresh privileges: flush privileges; Try connecting using node after you do so. If that doesn’t work, try it without @’localhost’ part.
You can actually do this one of two ways: MySQL update join syntax: UPDATE tableA a INNER JOIN tableB b ON a.name_a = b.name_b SET validation_check = if(start_dts > end_dts, ‘VALID’, ”) — where clause can go here ANSI SQL syntax: UPDATE tableA SET validation_check = (SELECT if(start_DTS > end_DTS, ‘VALID’, ”) AS validation_check FROM … Read more
If the database you want to restore doesn’t already exist, you need to create it first. On the command-line, if you’re in the same directory that contains the dumped file, use these commands (with appropriate substitutions): C:\> mysql -u root -p mysql> create database mydb; mysql> use mydb; mysql> source db_backup.dump;
767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It’s 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards). You also have to be aware that if you set an index on a big char or … Read more