Need HA in Mysql GTID based replication

I am using GTID based replication in mysql 5.7.I have few question

 1.How we can ensure here slave databases is consistent with master
   database ?
 2.How failover will work if master is down ?
 3.How to ensure High Availability in Mater/slave GTID replication ?````

Go to Source
Author: Uday Raj Gupta

MySQL shutdown unexpectedly in xampp

  • List item

here is my error log

InnoDB: using atomic writes.
2020-06-27 21:06:20 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-06-27 21:06:20 0 [Note] InnoDB: Uses event mutexes
2020-06-27 21:06:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-06-27 21:06:20 0 [Note] InnoDB: Number of pools: 1
2020-06-27 21:06:20 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-06-27 21:06:20 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2020-06-27 21:06:20 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-27 21:06:21 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-06-27 21:06:21 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-27 21:06:21 0 [Note] InnoDB: Setting file 'C:xamppmysqldataibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-27 21:06:21 0 [Note] InnoDB: File 'C:xamppmysqldataibtmp1' size is now 12 MB.
2020-06-27 21:06:21 0 [Note] InnoDB: Waiting for purge to start
2020-06-27 21:06:21 0 [Note] InnoDB: 10.4.13 started; log sequence number 47161; transaction id 9
2020-06-27 21:06:21 0 [Note] InnoDB: Loading buffer pool(s) from C:xamppmysqldataib_buffer_pool
2020-06-27 21:06:21 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-06-27 21:06:21 0 [Note] InnoDB: Buffer pool(s) load completed at 200627 21:06:21
2020-06-27 21:06:21 0 [Note] Server socket created on IP: '::'.

Go to Source
Author: Abhishek Regmi

Can InnoDB data at rest encryption be overriden

I am exploring options for distributing a MySQL/MariaDB database with my app. I want to ensure users (even the root user) cannot view or manipulate data in my database/tables. Recent releases of MySQL and MariaDB have data-at-rest capabilities:

MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/

MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

But what is not clear to me is: Can the MySQL root user reset the encryption password on my database/tables to view or manipulate the contents?

Go to Source
Author: TSG

Fresh install OK, upgrading PHP 5.4 -> 7.3 fails with “Use of undefined constant WP_CONTENT_DIR”

On my HostGator shared hosting with a CPanel option to set PHP per domain, if I turn on PHP 7.3 (or other 7.x versions) for my existing blog I get an error page:

Warning: Use of undefined constant WP_CONTENT_DIR – assumed ‘WP_CONTENT_DIR’ (this will throw an Error in a future version of PHP) in /my_site/public_html/wp-includes/load.php on line 141
Your PHP installation appears to be missing the MySQL extension which is required by WordPress

If I Google this, all the advice is about how to install mysqld, but it’s surely already installed as doing a fresh install of WordPress on a separate sub-domain works perfectly under PHP 7.3, so I must be missing something in my older site’s WordPress wp-config.php, perhaps. Looking at working and failing versions, though, I cannot see an obvious difference.

Go to Source
Author: Ken Y-N

Copying a MySQL 5.6 server settings for a new environment?

I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server. It’s been having issues (The Windows 8, which is then causing the MySQL to have issues). Therefore I was tasked with putting the MySQL 5.6 Server on a Windows 10 VM for stability reasons. The amount of ram the server is allowed and other settings have been tweaked over time. Is there an way I can export these settings, so when I install MySQL on my Windows 10 VM, I can just tell it to use the same?

Go to Source
Author: bjk116

ANSWER

This should be as straightforward as copying the configuration file – my.ini is the default – of the old MySQL server to the new one. It is a regular text file so copying it should not be an issue at all.

If there are other supporting configuration files for the DB server you might have customized, copy those as well.

Restart the MySQL server as necessary. If there are issues, like for example when using InnoDB engine, deleting the datadir should fix that quickly. Of course, I’m assuming this is on a fresh MySQL install. Don’t do this otherwise. You can migrate the data properly through a mysqldump for example.

Generate all combinations for variables and insert into temp table

0

I have one requirement there are 4 variables and i want all combinations of 4 variables and insert into temp table .

DECLARE StartDateTime DATETIME;
DECLARE Age INT; 
DECLARE Duration INT ;
DECLARE TotalDD INT;

CREATE TEMPORARY TABLE tempTable(
    Duration INT,
    TotalDD INT,
    Age INT,
    StartDateTime DATETIME,
   
);
SET Age = 16;
SET TotalDD = 14;
SET Duration = 30;
SET StartDateTime = CURDATE();

Excepted Result:

Duration age TotalDD StartDateTime
30 null null null
null 16 null null
30 null null null
30 16 null null
null null 14 20200622
30 null 14 null
30 16 null 20200622

……….. ………. so on

Go to Source
Author: user202

common columns in all tables in mysql

I want to create a table like base_table with below columns –
id, created_at, created_by.

and for all other tables, I want created_at and create_by columns available through inheritance.
I don’t want to create these common columns in all other tables.

Go to Source
Author: zeeshank1

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB.

The first one has columns “Offset, Size, and ColumnToBeIndexed” with BIGINT “Offset” being the primary key, and the second one has columns “OffsetAndSize, and ColumnToBeIndexed” with BIGINT “OffsetAndSize” being the primary key. And there are both 15 millions rows in each table.

I added indexes to both tables on “ColumnToBeIndexed.

My two queries for them are

SELECT Offset, Size 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY Offset ASC

and

SELECT OffsetAndSize 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY OffsetAndSize ASC

Because the second query could use the secondary index and does not need to look up the clustered index to find the “size” information, I expected that the second query on the second table performs better than the first query on the first table. However, as the test came out, it turned out that the first query performs better every single time.

Does anybody out there know what seems to be the problem?

Go to Source
Author: Bruce

Are there turn-key development webapp containers for evaluation?

In short: I want a ready-made webapp framework with a web server and a database in a container, such that I can edit files on my local drive and view the results through a browser pointed at the container – in order to get started with said framework. I want to be writing and viewing webpages within 10 minutes of starting. Seems achievable in theory.

If that’s clear, feel free to skip the rest…

Every few years I check in on the web development world, which I am not involved in. I always bail out because the tools take too long to set up and rarely work without lots of tinkering. I would think by now there should be some simple, turn-key docker-compose solutions that can give a reasonable working setup (ie. Node.js+MySQL, LAMP, etc).

Ideally it would look to a configurable directory on the local disk for the actual code to run, so I can just start working on a project. I am not locked in to any platform, but it should be up and running (serving pages) inside of 10 minutes. Shouldn’t have to configure more than a couple ports, passwords, and directories.

I have found tutorials that get halfway to setting it up, but not one actually works as advertised. After having spent the better part of the past two days trying solutions, I am about ready to give up yet again. My goal is not to get into the minutiae of setting up a deployment. I just want to mess around with the frameworks and languages and see what they are capable of and what I might like to use.

Is there something out there I am just not seeing? Is there something that prevents this? Wouldn’t this be the preferred method of starting a project for just about everyone, experienced or not?

It seems like a few of the common stacks would be really handy.

If I see something that requires making custom Docker files, I am out. I’ve been down that road (quite extensively in the supercomputing realm with Singularity), and it seems entirely unnecessary here. The official containers for these tools seem okay, they just need to be integrated and brought up together.

This and this (ignore the terrible politics splashed all over the site) seemed to get close in terms of the goal, but it did not work in practice and needs debugging. Even then, trying it required reading through a lot of unnecessary stuff and copying and pasting pieces of multiple versions of files.

I would think the maintainers of projects would have demo versions ready to go. It would probably attract new users.

In many cases, the final deployment very well may look like the development one, which would be a nice bonus.

Go to Source
Author: ThisShouldBeSimpleNow

Connecting to mysql db server from a remote server to a local machine

As per my understanding, there are two main things to check before connecting to a remote mysql db server.

1. Bind address.
2. Grant privileges.

I was able to connect to a remote mysql db server from my Mac, it was easy (mysql -uroot -h x.x.x.x -p)

Imagine a scenario for my own learning, I want to connect to a mysql db instance on my Mac from a remote server. Imagine I am logging into this remote linux machine from my Mac and trying to connect to my own Mac db server?

mysql -uroot -h x.x.x.x -p

is raising an error 2003 mysql cannot connect 10060 cannot connect to the x.x.x.x server. It is similar to the reverse proxying while connecting to the local machine from a remote one, can someone direct to right resource (if there is a similar question please comment) which can help me figure out this thing?

Go to Source
Author: JumpMan

Phpmyadmin access denied every time ubuntu restarts

I have a xampp installation on my ubuntu machine. And I am trying to use phpmyadmin to access mysql. But every time I start xampp and try to open phpmyadmin I get an error that says mysqli::real_connect(): (HY000/2002): Connection refused. I have read some questions and changing mysql password seems to fix this. But I don’t want to change mysql password every time I restart my pc. Is there any way to fix this? Any help would be appreciated. Thanks

Go to Source
Author: Abdulaziz Yesuf

How to reproduce SQL Injection problem by sending single quote in MySQL?

This is Damn Vulnerable Web Application (DVWA) and it’s vulnerable to SQL injection (SQLi).

Let’s begin by sending normal request

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit#

Output via browser

ID: 1
First name: admin
Surname: admin

This is how the request looks like in MySQL

mysql> SELECT first_name, last_name FROM users WHERE user_id = '1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| admin      | admin     |
+------------+-----------+
1 row in set (0.00 sec)

mysql> 

Common way to identify SQL injection is by sending single quote ' char in the parameter.

E.g. id='

Give it a try on the url and it works.

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id='&Submit=Submit#

Web browser will display SQL error indicates that the site is vulnerable to SQLi

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1

I didn’t know how the query looks like in MySQL ..

So I’ve tried SELECT first_name, last_name FROM users WHERE user_id = '''; but I didn’t get the same error.

Instead, I was getting '> symbol from MySQL shell.

mysql> SELECT first_name, last_name FROM users WHERE user_id = ''';
    '> 
    '> 
    '> '
    -> 
    -> ;
Empty set (0.00 sec)

mysql> 

What is the right way to query id=' or user_id = ' (single quote) request in MySQL?

Go to Source
Author: Wolf

Try to run Python programm from command line. ImportError: No module named mysql.connector

I’m new in programming. So I have a question about this error.
I’m trying to run programm from command line in PyCharm, and get this Error.

root@kali:~# python /root/PycharmProjects/lesson/ProL3/ner.py
Traceback (most recent call last):
File “/root/PycharmProjects/lesson/ProL3/ner.py”, line 2, in
import mysql.connector as sql
ImportError: No module named mysql.connector

Prog:

import mysql.connector as sql
from mysql.connector import Error
import argparse


connection = sql.connect(
    host="localhost",
    password="mroot",
    user="myro"
)
mycursor = connection.cursor()

parser = argparse.ArgumentParser(description='Database management', usage='Script option:')
parser.add_argument('-d', '--data', help="Create Databases")
args = parser.parse_args()

class Data:
    def __init__(self, database=''):
        self.database = database
        self.create_database()

    def create_database(self):
        try:
            mycursor.execute(self.database)
            print("The database was created!")
        except Error as e:
            print(e)


What should I do?

Go to Source
Author: Boboc

Altering big table with gh-ost on Google Cloud

Altering big table with gh-ost on Google Cloud

We need to add some indexes and one additional column to a large MySQL table.

MySQL is Google Cloud SQL version 5.7.25-google-log.

Some table info:

  • currently holding 1 331 363 322 rows
  • size of the table 409GB
  • size of the index on the table 473GB
  • every day we crate around 2 000 000 new rows and update around 50 000 of old rows
  • no foreign keys

We tried adding indexes directly to this table but we had to stop it because of this issue: https://www.percona.com/blog/2019/06/27/innodb-alter-table-add-index-and-insert-performance/

So, we decided to go with the gh-ost. Currently, this tool is running for 240h31m30s and it is at 51.8% with the current progress of 2.6% per day and it slowing down every day.
To speed up the things we tried to play with chunk-size, Threads_running, dml-batch-size but none of them helped to speed up the things. We only saw an increase in replication lag on replicas.
On Google Cloud SQL instance you can’t enable bin log on the replicas so we had to run it against the master (link, link).

Also, I have created an issue with all parameters that we use and some sample of logs on gh-ost repo: https://github.com/github/gh-ost/issues/845

Is there anything else that we could try to tweak or should we try some other tool?

Go to Source
Author: ffox003