Kategoriarkiv: SQL

Logging af ./mysql queries til en tekst-fil

Det er ikke rocket science, man det er rart at have ved hånden når man har brug for det 😉 mysql> tee /tmp/dump.log Logging to file ‘/tmp/dump.log’ mysql> show processlist; +——-+———-+———–+———-+———+——+——-+——————+ | Id | User | Host | db | … Læs resten

Udgivet i Knowledge Base, Linux, Old Base, SQL | Skriv en kommentar

MySQL multimaster replikation med Galera

Multimaster replikation er når en eller flere database servere er koblet sammen og ændringer på en vilkårlig maskine effektueres på samtlige. Metoden beskrevet i dette indlæg er basseret på en tredieparts patch til MySQL og jeg vil demonstrere hvordan man … Læs resten

Udgivet i Knowledge Base, Linux, Old Base, SQL | Skriv en kommentar

Recover MySQL root password

/etc/init.d/mysql stop /usr/bin/mysqld_safe –skip-grant-tables –skip-networking & mysql -u root use mysql; UPDATE user SET Password=PASSWORD(“your password here”) WHERE User=”root”; exit # kill all the mysql processes /etc/init.d/mysql stop # Start MySQL again /etc/init.d/mysql start

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

Extract a mysql query to XML

echo “select * from table” | mysql -B –xml -h [host] -u [user] -p[password] [database] > output.xml not all of the parameters are needed of course. use –table if you want the nice ascii tables around the result if -B … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

MySQL: Getting a random roll of the dice

CREATE TABLE dice ( d_id int(11) NOT NULL auto_increment, roll int, PRIMARY KEY (d_id) ); insert into dice (roll) values (1); insert into dice (roll) values (2); insert into dice (roll) values (3); insert into dice (roll) values (4); insert … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

MySQL: MERGE: Several tables can be merged into one

CREATE TABLE log_01 ( pkey int(11) NOT NULL auto_increment, a int, b varchar(12), timeEnter timestamp(14), PRIMARY KEY (pkey) ) type=MyISAM; CREATE TABLE log_02 ( pkey int(11) NOT NULL auto_increment, a int, b varchar(12), timeEnter timestamp(14), PRIMARY KEY (pkey) ) type=MyISAM; … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

Mysql transactions

Transactions: Not all table types support transactions. BDB and INNODB type do support transactions. Assuming the server has NOT been started with –skip-bdb or –skip-innodb the following should work: mysql> create table tran_test (a int, b int) type = InnoDB; … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

Mysql show status information on table / show create

Show status information on a table. Note, if the database was started with –safe-show-database or –skip-show-database some of these commands may not work. Note the “\G” option may provide a nicer format. Show the create statement: mysql> show create table … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

How to create and use temporary tables in MySQL?

The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be know for temporary tables is that they will be deleted when the current client disconnects You could try to … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar

MySQL: Find out who is doing what, and kill the process if needed.

mysql> show processlist; show processlist; +—–+——+———–+———+———+——-+——-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—–+——+———–+———+———+——-+——-+——————+ | 657 | prog | localhost | weather | Sleep | 28619 | | NULL | … Læs resten

Udgivet i Knowledge Base, Old Base, SQL | Skriv en kommentar