recentpopularlog in

racl101 : mysql   487

« earlier  
Backup (mysql dump) all your MySQL databases in separate files - Running with Ruby
Really good, simple to understand Bash shell script demonstrating how to backup all the databases in a given mysql server installation into a single file or into separate files (one for each database).
blog  tutorial  howto  example  guide  reference  backup  export  database  dump  sql  files  shell  script  single  separate  mysql  bash 
9 days ago by racl101
Installing MySQL (with Debconf) | Servers for Hackers
TLDR:

echo mysql-server mysql-server/root_password password root | debconf-set-selections;\
echo mysql-server mysql-server/root_password_again password root | debconf-set-selections;\
apt-get install -y mysql-server mysql-client libmysqlclient-dev

So that you don't have install mysql server interactively and can set the root password programmatically.


Works for installing MySQL 5.7 on Ubuntu 16.04 as well.
blog  tutorial  screencast  video  howto  install  mysql  mysql5.7  server  debian  configuration  root  password  programmatically  ubuntu  ubuntu16.04  script  bash  shell  example  guide  reference 
4 weeks ago by racl101
Script to open a mysql database in Sequel Pro from a service in docker-compose.
A gist of an Bash Shell Script to open up Sequel Pro MacOS / MacOS X app with credentials to view a MySQL database running within a Docker container.
github  gist  script  bash  shell  open  sequel  pro  sequelpro  database  mysql  docker  container  credentials  totry  guide  reference 
4 weeks ago by racl101
Connecting to a mysql running on a Docker container · Issue #95 · docker-library/mysql
Frankly, this forum page has no consensus on a solution to this issue. Nevertheless, what worked for me on my set up with running MacOS as the Docker host and running a container with: Ubuntu 16.04 and MySQL 5.7 is the following:

1) Within the container: change in /etc/mysql/mysql.conf.d/mysqld.cnf the bind-address option from 127.0.0.1 to 0.0.0.0
2) Restart MySQL server (note that whenever you start MySQL you must use this command in the Dockerfile:

RUN sed -i -e"s/^bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/" /etc/mysql/mysql.conf.d/mysqld.cnf
RUN find /var/lib/mysql -type f -exec touch {} \; && service mysql start

That is, you must touch the files in /var/lib/mysql and then start the server with service command and not systemctl.

Next, create a user separate from root e.g. dev_1, in MySQL and given them privileges to their '0.0.0.0' and '%' hosts. Like this in the Dockerfile:

RUN find /var/lib/mysql -type f -exec touch {} \; && service mysql start && mysql -uroot -proot mysql -e "GRANT ALL ON *.* TO root@'0.0.0.0' IDENTIFIED BY 'secret' WITH GRANT OPTION; CREATE USER 'dev_1'@'0.0.0.0' IDENTIFIED BY 'secret'; GRANT ALL ON *.* TO 'dev_1'@'0.0.0.0' IDENTIFIED BY 'secret' WITH GRANT OPTION; GRANT ALL ON *.* TO 'dev_1'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Then when you start the container you must publish the port number like so:

docker run -t -d -p 4406:3306 ubuntu_16_04_lemp_php_7_2:latest

If your container stops prematurely, then you might want to add a CMD line in your docker file to run a start.sh script and within that script you might want to add something like this:

# stupid hack to keep container from being stopped
tail -f /dev/null

Finally, to connect to your DB instance on your local MacOS machine with an app like Sequel Pro for example, use the following credentials:

Host: 0.0.0.0
Username: dev_1
Password: <dev_1_password>
Port: 4406

If you don't like to use port 4406, try 3307, 3308, 3309.
github  forum  howto  example  troubleshoot  mysql  server  docker  host  connection  issue  sequel  pro  mariadb  client  hostname  docker-machine  ip  address  ipaddress  guide  reference  toread  tolearn  totry  tounderstand  image  port  detached  database  credentials 
4 weeks ago by racl101
SQLAlchemy Documentation — SQLAlchemy 1.2 Documentation
Seems that the key to learning to work with databases in Python requires understanding SQL Alchemy.
documentation  guide  reference  sqlalchemy  python  database  toolkit  driver  orm  relational  engine  mysql  sqlite3  tutorial  howto  example  resource 
9 weeks ago by racl101
MySQL :: MySQL 5.7 Reference Manual :: 4.2.6 Using Option Files
Check out the table under the heading: "Option Files Read on Unix and Unix-Like Systems" to see where (and in what order) MySQL checks for options.

Of specific importance for logging in passwordless, at least with Ansible, is the ~/.my.cnf file.
mysql  server  config  documentation  guide  reference  my.cnf  user  howto  example  toread  tolearn  totry  tounderstand 
july 2018 by racl101
Ansible Install MySql 5.7 - Set Root User Password - Stack Overflow
This response explains a nuance for MySQL 5.7 whereby you can't log in as a root user with no password.

To change the password for a root user for the first time you have to do something like this:

- name: Check if '~/.my.cnf' exists
stat:
path: ~/.my.cnf
register: my_cnf_check

- name: Update MySQL root password for all root accounts
mysql_user:
name: root
host: "{{ item }}"
password: "{{ mysql_root_password }}"
state: present
check_implicit_admin: "{{ 'no' if my_cnf_check.stat.exists == True else 'yes' }}"
with_items:
- 127.0.0.1
- ::1
- localhost
become: "{{ 'no' if my_cnf_check.stat.exists == True else 'yes' }}"
become_method: sudo

- name: Create MySQL '~/.my.cnf' config file and add root credentials into it
template: src=templates/mysql_my.cnf.j2 dest=~/.my.cnf mode=0600
stackoverflow  ansible  mysql  mysql5.7  howto  example  guide  reference  update  root  user  password  my.cnf  sudo  admin 
july 2018 by racl101
Error during install , "python mysqldb module is required" · Issue #42 · geerlingguy/ansible-role-mysql
If using python3 as the Ansible Python Interpreter then you'll need to install both:

- python3-pip
and
- python3-mysqldb

to be able to update mysql user passwords with Ansible.
github  forum  mysql  module  howto  fix  troubleshoot  python  python3  ansible  update  root  password  user  example  guide  reference  pip 
july 2018 by racl101
Update Amazon RDS max_connections with Parameter Group · Issue #16 · jollygoodcode/jollygoodcode.github.io
This thread discussion might provide some insight into manually increasing the mysql max_connections option / setting manually and overriding Amazon's AWS "sensible" defaults.
github  forum  aws  amazon  howto  override  change  mysql  database  db  option  variable  max_connections  setting  totry  example  guide  reference 
april 2018 by racl101
MySQL 5.7.12 import cannot create a JSON value from a string with CHARACTER SET 'binary' - Stack Overflow
For single , double quoting issue reasons you might elect to encode your database dump containing a column containing a column that holds JSON data (MySQL sees this as blob data). Then you might need to import this dump again. In those cases you can decode the hexadecimal strings using the MySQL CONVERT function into the original string with utf8mb4 encoding.This is how you do it.
stackoverflow  mysql  mysql5.7  mysqldump  dump  blob  data  utf8mb4  column  convert  decode  hexadecimal  howto  example  guide  reference 
april 2018 by racl101
MySQLdb User's Guide
I don't get it, but I guess you're supposed to use %s as a placeholder even for integers (so not %d) in a query string that is used in a cursor.execute() statement. I tried it with pymysql. I'm not sure why, but it worked.

This is the explanation: In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".
mysql-python  howto  troubleshoot  cursor  mysql  pymysql  query  python  python2.7  string  format  number  error  fix  guide  reference  examples 
april 2018 by racl101
mysql - MySQLdb Python insert %d and %s - Stack Overflow
I don't get it, but I guess you're supposed to use %s as a placeholder even for integers (so not %d) in a query string that is used in a cursor.execute() statement. I tried it with pymysql. I'm not sure why, but it worked.
stackoverflow  howto  troubleshoot  cursor  mysql  pymysql  query  python  python2.7  string  format  number  error  fix  guide  reference 
april 2018 by racl101
insertion - Python MySQLdb issues (TypeError: %d format: a number is required, not str) - Stack Overflow
I don't get it, but I guess you're supposed to use %s as a placeholder even for integers (so not %d) in a query string that is used in a cursor.execute() statement. I tried it with pymysql. I'm not sure why, but it worked.
stackoverflow  howto  troubleshoot  cursor  mysql  pymysql  query  python  python2.7  string  format  number  error  fix  guide  reference 
april 2018 by racl101
Welcome to PyMySQL’s documentation! — PyMySQL 0.7.2 documentation
The documentation is kind of sparse, but it has some good reference links. That's where the meat is.
python  library  pymysql  module  database  mysql  query  api  guide  reference  documentation 
march 2018 by racl101
Ubuntu 16 - how to increase maximum file open limit ( ulimit -n )
What's interesting here is that this tutorial points out that:

/proc/sys/fs/file-max

is the kernel limit to which limits in /etc/security/limits.conf can be increased. Other tutorials don't really mention it.
blog  tutorial  howto  example  guide  reference  ubuntu  ubuntu16.04  maximum  open  file  handle  limit  linux  server  ulimit  nginx  mysql 
november 2017 by racl101
ulimit - Ubuntu 16.04 Server MySql open_file_limit won't go higher than 65536 - Server Fault
Really good example to follow in case increasing max_connections and increasing limits in /etc/security/limits.conf doesn't work.
serverfault  stackexchange  howto  create  mysql  service  override  configuration  file  increase  maximum  open  limit  database  server  example  guide  reference  ubuntu  ubuntu16.04  linux 
november 2017 by racl101
« earlier      
per page:    204080120160

Copy this bookmark:





to read