This can be set on your server as it's running: set global max_allowed_packet=104857600; This sets it to 100MB. ---------------------------------------------------------- Edit the file /etc/my.cnf: [mysqld] max_allowed_packet=16M ----------------------------------------------------------

ALTER TABLE tableName CHANGE `oldcolname` `newcolname` datatype(length); ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

Add Columns to a Table: ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column]; ALTER TABLE vendors ADD COLUMN phone VARCHAR(15) AFTER name; ALTER TABLE vendors ADD COLUMN vendor_group INT NOT NULL; ALTER TABLE vendors ADD COLUMN email VARCHAR(100) NOT NULL, ADD COLUMN hourly_rate decimal(10,2) NOT NULL; ----------------------------------------------------------------------

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

1- /etc/init.d/mysql stop 2- Using the following command find the processes which use mysql and (kill -9 pid) to stop them: ps aux | grep mysql 3- /usr/sbin/mysqld --skip-grant-tables --skip-networking & 4- mysql -u root 5- FLUSH PRIVILEGES; 6- Reset/update your password: SET PASSWORD FOR root@'localhost' = PASSWORD('password'); If you have a mysql root account that can connect from everywhere, you should also do: UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; And if you have a root account that can access from everywhere: USE mysql UPDATE user SET Password = PASSWORD('newpwd') WHERE Host = '%' AND User = 'root'; 7- FLUSH PRIVILEGES; 8-/etc/init.d/mysql start

GRANT INSERT, SELECT, DELETE, UPDATE ON <your_database_name>.* TO 'user'@'localhost' IDENTIFIED BY ' '; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

We need at least 3 servers in a network. 1- apt-get install galera-3 galera-arbitrator-3 default-mysql-server rsync ---------------------------------------------------------------- 2- Create the following file with the content: vim /etc/mysql/conf.d/galera.cnf [mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="test_cluster" wsrep_cluster_address="gcomm://first_ip,second_ip,third_ip" # The first_ip in here is 10.10.0.101 # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="10.10.0.101" wsrep_node_name="node1" DO THE SAME for the other two servers. Change the last two lines based on the server's configs. ---------------------------------------------------------------- 3- vim /etc/mysql/mariadb.conf.d/50-server.cnf Comment: bind-address = 127.0.0.1 DO THE SAME for the other two servers. ---------------------------------------------------------------- Shut down mysql on all of the servers: 4- systemctl stop mysql ---------------------------------------------------------------- 5- On the first server: # galera_new_cluster On the 2nd & 3rd servers: systemctl start mysql ----------------------------------------------------------------

set password for root@localhost=PASSWORD('');

show databases; --------------------------------------------------------------------------------------------- SELECT * FROM trunk WHERE status like '%unre%' and date_time BETWEEN DATE_SUB(NOW(), INTERVAL 4 DAY) AND NOW(); ---------------------------------------------------------------- SELECT count(*) as errors FROM trunk WHERE status like '%unre%' and date_time BETWEEN DATE_SUB(NOW(), INTERVAL 4 DAY) AND NOW(); ---------------------------------------------------------------- select * from cdr order by id desc limit 1; select * from (select * from cdr order by acctid) as t1 order by acctid desc limit 100\G ---------------------------------------------------------------- show tables from asterisk; ---------------------------------------------------------------- show columns from cdr; ---------------------------------------------------------------- SELECT UNIQUE VALUE: SELECT DISTINCT mycolumn FROM mytable ---------------------------------------------------------------- List columns with indexes: SHOW INDEX FROM mytable; ----------------------------------------------------------------

This link provide more than just a remote connection! It provides security too but I don't need it right now. So if for now security is not important to you, use the summary below: http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html -------------------------------------------------------------------------------------------- Binding is limited to either 0, 1, or all IP addresses on the server. That means you can not provide more than one IP address at the same time. nano /etc/mysql/my.cnf bind-address = 0.0.0.0 /etc/init.d/mysql restart And then in mysql console: mysql -u root -p GRANT ALL PRIVILEGES ON your_database.* TO 'root'@'88.135.38.2' IDENTIFIED BY 'passw0rd' WITH GRANT OPTION;

It's different from the replace() method in python :O UPDATE table SET field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%'; 'string' is the value to be found in the '%string%' 'anothervalue' is the value to be replaced.

show databases; ----------------------------------------- use a_database; show tables; SHOW COLUMNS FROM City; -----------------------------------------

select * from (select * from cdr order by acctid) as t1 order by acctid desc limit 200;

You need to tell MySQL which database to use first: USE database_name; And here is a sample table: CREATE TABLE cdr ( calldate datetime NOT NULL default '0000-00-00 00:00:00', clid varchar(80) NOT NULL default '', src varchar(80) NOT NULL default '', dst varchar(80) NOT NULL default '', dcontext varchar(80) NOT NULL default '', channel varchar(80) NOT NULL default '', dstchannel varchar(80) NOT NULL default '', lastapp varchar(80) NOT NULL default '', lastdata varchar(80) NOT NULL default '', duration int(11) NOT NULL default '0', billsec int(11) NOT NULL default '0', disposition varchar(45) NOT NULL default '', amaflags int(11) NOT NULL default '0', accountcode varchar(20) NOT NULL default '', uniqueid varchar(32) NOT NULL default '', userfield varchar(255) NOT NULL default '' );

Export: mysqldump -u [username] -p [database_name] > [dumpfilename.sql] Import: mysql -u [username] -p [database_name] < [dumpfilename.sql] ------------------------------------------------------------------------ Export data to CSV file: SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; ------------------------------------------------------------------------ Export data to CSV file (From multiple table + multiple Fields): select table_1.field_1, table_1.field_2, table_2.field_1, table_3.field_7 from table_1, table_2, table_3 into outfile '/tmp/data.csv' fields terminated by ',' enclosed by "" lines terminated by '\n'; ------------------------------------------------------------------------ Import CSV file directly into MySQL: LOAD DATA INFILE '/tmp/cdr.csv' INTO TABLE cdr FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; The IGNORE is used for the header of file (if you have created that file manually and it might have titles like in excel, name, family, id...) ------------------------------------------------------------------------ Import ".sql" files: $ mysql -u root db_name < db.sql ------------------------------------------------------------------------

1- mysql -u root -p 2- create database demodb; 3- INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword')); OR you might need the following based on the installed mysql version: INSERT INTO mysql.user (User,Host,authentication_string, ssl_cipher, x509_issuer,x509_subject) VALUES('dianomi','localhost',PASSWORD('dfg3253'),'','',''); 4- FLUSH PRIVILEGES; 5- GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost; 6- FLUSH PRIVILEGES;

1- Configure MySQL PPA wget http://repo.mysql.com/mysql-apt-config_0.8.9-1_all.deb dpkg -i mysql-apt-config_0.8.9-1_all.deb 2- Install MySQL apt update apt-key adv --keyserver keyserver.ubuntu.com --recv-keys <the_GPG_key> apt install mysql-server python-dev python3-dev default-libmysqlclient-dev 3- Secure MySQL Installation (You might not need this step for PC/Laptop or testing environments) systemctl restart mysql mysql_secure_installation 4- Connect MySQL mysql -u root -p